Clever Geek Handbook

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.

+3


source to share


3 answers 


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' )


+1


source


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)
       )
;
+1


source


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'
+1


source






More articles:


All Articles