SQL Query Reference Sheet

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.