CODEWITHSUNDEEP
mysqljoinexists
Jon
Jon

Reputation: 13002

mysql select ids from one table which are not found in another table under certain conditions

Using the following two (simplified) table structures I'd like to find all DISTINCT session_id values that appear in TABLE1 where flag = yes that DO NOT appear in TABLE2 where progress = 11.

TABLE1 has no duplicate session_id values.

TABLE1

id_table1 | session_id | flag
------------------------------
1         | abcd       | yes
2         | efgh       | no
3         | ijkl       | yes
4         | mnop       | yes
5         | vwxyz      | yes

TABLE2

id_table2 | session_id | progress
---------------------------------
1         | abcd       | 3
2         | efgh       | 11
3         | ijkl       | 2
4         | ijkl       | 7
5         | mnop       | 11
6         | vwxyz      | 10
7         | vwxyz      | 11

The expected results here are:

abcd
ijkl

Upvotes: 1

Views: 1153

Answers (2)

Michael J.V.
Michael J.V.

Reputation: 5609

SELECT 

DISTINCT t1.session_id 

FROM
    id_table1 t1 

INNER JOIN 
    id_table2 t2 
ON 
    t1.session_id = t2.session_id 

WHERE
    t1.flag = 'yes'
AND
    t2.progress NOT IN(11)

Upvotes: 1

Andrew
Andrew

Reputation: 5277

select distinct session_id 
from table1 where session_id not in (select session_id from table2) and flag='yes'

Upvotes: 0

Related Questions