A variety of SQL query examples, lots of basic stuff taken from basic tutorials as well as more personally-useful stuff.
ArchivesSpace MySQL Queries
Access Restrictions
SELECT rr.archival_object_id, #or resource_id, or...
rr.restriction_note_type,
rr.begin,
rr.end,
note.notes,
# archival_object.title,
# npi.persistent_id,
rrt.restriction_type_id,
enumeration_value.value
# , CONCAT('/repositories/', archival_object.repo_id, '/archival_objects/', archival_object.id) AS AO_URL
, CAST(note.notes as CHAR (10000) CHARACTER SET UTF8) AS restriction_text
FROM rights_restriction rr
LEFT JOIN note on rr.archival_object_id=note.archival_object_id
# LEFT JOIN note_persistent_id npi on note.id=npi.note_id
LEFT JOIN rights_restriction_type rrt on rr.id=rrt.rights_restriction_id
LEFT JOIN enumeration_value on rrt.restriction_type_id=enumeration_value.id
#LEFT JOIN archival_object ON rr.archival_object_id=archival_object.id
Basic SQL Stuff
Super basic stuff I don’t want to screw up live.
Base update/delete queries
UPDATE table_name
SET col1='val1', col2='val2'
WHERE key='keyvalue' [AND/OR] col2='val2';
DELETE FROM table_name
WHERE key='keyvalue' [AND/OR] col2='val2';
Insert Into
INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);
Columns don’t have to specified in the first line, but they determine specific columns where things will be inserted. Depending on needs and specificity.
Unique results
SELECT DISTINCT col1, col2
FROM table_name
WHERE condition='fulfilled';
Order by
ORDER BY col1, col2 ASC|DESC;
Ascending by default. May order by all columns if decided.
SQL Joins
Fuuuuuuuucking SQL Joins. Anyway, in theory:
SELECT table_1.col_a, table_1.col_d, table_2.col_c
table_1 JOINTYPE table_2
ON (table_1.col_a=table_2.col_f)
WHERE col_b='value'
Where JOINTYPE = the type of join— right, inner, etc.
The “database_name.” can be affixed to the beginning of table names.
Inner Join
Returns a result row when both paired columns have matching values.
Outer Join
Returns a result row when either table has a column with the matching value.
Left Outer Join
Left, in this case, refers to the first table in the pairing. It’ll match every line in the first table and designated data from the second if it exists.
SELECT product.id AS "ID", product.name AS Name, "$" + stock.price AS Price, stock.number AS "Stock Count"
FROM product LEFT JOIN stock
ON product.id=stock.id
For a pair of tables where:
1, Teddy Bear
2, Stuffed Gorilla
3, Stuffed Octopus
and
1,$14.99,29
2,,
3,,24
This will return:
Right Outer Join
Right, in this case, refers to the second table in the pairing. It’ll match every line in the second table and designated data from the second if it exists. See above example, only to the right vs. the left.




