SQL JOIN tables with OR'd WHERE on both tables

example.sql

# join table, no WHERE
SELECT s.id, tf0.value as iras_id FROM studies s LEFT JOIN text_fields tf0 ON s.id = tf0.study_id AND tf0.text_field_definition_id = (SELECT id from text_field_definitions WHERE `key` = 'csp_number') ORDER BY s.id

# join table, WHERE value in joined table
SELECT s.id, tf0.value as iras_id FROM studies s JOIN text_fields tf0 ON s.id = tf0.study_id AND tf0.text_field_definition_id = (SELECT id from text_field_definitions WHERE `key` = 'csp_number') AND tf0.value = '7713' ORDER BY s.id

# join two tables, no WHERE
SELECT s.id, tf0.value as iras_id, tf1.value as rec_ref FROM studies s JOIN text_fields tf0 ON s.id = tf0.study_id AND tf0.text_field _definition_id = (SELECT id from text_field_definitions WHERE `key` = 'csp_number') JOIN text_fields tf1 ON s.id = tf1.study_id AND tf1.text_field_definition_id = (SELECT id from text_field_definitions WHERE `key` = 'rec_ref') ORDER BY s.id

# join two tables, WHERE value on t1 is X OR on t2 is Y
SELECT s.id, tf0.value as iras_id, tf1.value as rec_ref FROM studies s JOIN text_fields tf0 ON s.id = tf0.study_id AND tf0.text_field _definition_id = (SELECT id from text_field_definitions WHERE `key` = 'csp_number') JOIN text_fields tf1 ON s.id = tf1.study_id AND tf1.text_field_definition_id = (SELECT id from text_field_definitions WHERE `key` = 'rec_ref') WHERE tf0.value = '537' OR tf1.value = '12/NW/0251' ORDER BY s.id