CODEWITHSUNDEEP
mysql
Kobus Myburgh
Kobus Myburgh

Reputation: 1202

MySQL Find records in one table that has no value of a column in another table

I have table1, containing columns (simplified):

+-------------------------+
| id | user_id | username |
+----+---------+----------+
|  1 |     123 | peter    |
|  2 |     234 | john     |
+-------------------------+

and table 2, containing columns (simplified):

+----------------------------------+
| id | user_id | checklist_item_id |
+----+---------+-------------------+
|  1 |     123 |               110 |
|  2 |     123 |               111 |
|  3 |     123 |               112 |
|  4 |     234 |               110 |
|  5 |     234 |               112 |
+----------------------------------+

As shown above, Each entry for user_id from table1, has multiple entries for that user_id with multiple checklist_item_ids.

I am interested in returning ONLY records that does NOT have an entry in the second table for checklist_item_id = 111. The query must return only:

+---------+
| user_id |
+---------+
|     234 |
+---------+

As user with user_id 123 DO have an entry in table two with checklist_item_id of 111.

Upvotes: 0

Views: 28

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use corelated subquery

select t1.* from  table1 t1 where t1.user_id not in
( select user_id from table2 t2
   where t2.user_id=t1.user_id
   and checklist_item_id=111
)

Or use not exist which is efficient than not in 

select t1.* from table1 t1 where not exists
    ( select 1 from table2 t2  where t2.user_id=t1.user_id and 
    checklist_item_id=111
    )

DEMO in Fiddle

id  userid  itemid
4   234     110
5   234     112

In case you need only one id then it would be

select distinct t1.userid from  t1 where not exists
    ( select 1 from t1 t2  where t2.userid=t1.userid and 
    itemid=111
    )

output

userid
  234

Demo

Upvotes: 2

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

Simplest and efficient approach would be using LEFT JOIN, and filtering out those rows, where there is no matched record for checklist_item_id = 111

SELECT DISTINCT t1.user_id 
FROM table1 AS t1 
LEFT JOIN table2 AS t2 ON t2.user_id = t1.user_id AND 
                          t2.checklist_item_id = 111 
WHERE t2.user_id IS NULL

Upvotes: 1

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

You can use subquery, for example:

SELECT *
FROM table1
WHERE user_id NOT IN
    (SELECT user_id
     FROM table2
     WHERE checklist_item_id = 111)

Upvotes: 3

Related Questions