How to get row data based on the other table row data?-CodePudding

I'm not sure if I write my questions title correctly, but my example would probably explain it better.

Join table_1 and table_2 and get table_1 row, if there is no is_validated= 1. The query will get table_1, id 1 and 3, because they dont have is_validated = 1

table_1

table_2

id      table_1_id    is_validated 
1       1             0             
2       2             1             
3       2             0             
4       3             0             
5       3             0

expected result

id 
1       
3

I've try to use this query, but it doesn't get the result that I want.

SELECT * FROM table_1 t1
JOIN table_2 t2 ON t1.id=t2.table_1_id AND t2.is_validated = 0
GROUP BY t2.table_1_id

CodePudding user response：

Exists logic works nicely here:

SELECT t1.id
FROM table_1 t1
WHERE NOT EXISTS (SELECT 1 FROM table_2 t2
                  WHERE t2.table_1_id = t1.id AND t2.is_validated = 1);