SQL query to find records without references
I have two tables linked via an ID field:
table1: id, name, type
table2: id, id_table1, date, status
I need to collect all records table1
that have a specific value in a field type
and that are not listed in table2
, as well as all records table1
that are referenced in table2
that have a specific field value status
.
For the first part, if I remember correctly, I can use the command LEFT JOIN
:
LEFT JOIN table1.name
LEFT JOIN table2
ON table2.id_table1 = table1.id
WHERE (table1.value = 'value1') AND (table2.id_table1 IS NULL);
but for the second part I am lost ...
I am using MySQL 5.6 and I would like to define a view to handle this.
SELECT t1.*, t2.*
FROM table1 t1
LEFT JOIN table2 t2
ON table2.id_table1 = table1.id
WHERE (t1.type= 'value1' AND t2.id IS NULL)
OR (t2.status = 'certain status' )
I would think that you could just change WHERE to:
WHERE (table1.value = 'value1')
AND (table2.id_table1 IS NULL
OR
([the other table2 status criteria)
)
;
You can try this ...
SELECT T1.*,T2.*
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.Id=T2.Id_Table1
WHERE T1.Value = 'value1' AND T2.id_table1 IS NULL
UNION
SELECT T1.*,T2.*
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Id=T2.Id_Table1
WHERE T2.Status= 'Status Criteria'