CODEWITHSUNDEEP
sql
J Pizzle
J Pizzle

Reputation: 27

How to return only users where all of the user's account numbers are in another list

How can I do a join or some SQL to return the loginNames from table 1 which have all of their actNumber's listed in table 2?

table 1

loginName     actNumber
joe123        981651
joe123        916516
dan456        315654
dan456        316881
amy333        691654

table 2

actNumber
916516
315654
316881
691654

Upvotes: 0

Views: 39

Answers (2)

forpas
forpas

Reputation: 164139

You need a LEFT JOIN of table1 to table2 and group by loginname.
Then in the HAVING clause keep only the loginnames for which there is no null actnumber in table2:

select loginname
from table1 t1 left join table2 t2
on t2.actnumber = t1.actnumber
group by loginname 
having sum(case when t2.actnumber is null then 1 else 0 end) = 0

See the demo.
Results:

> | loginname |
> | :-------- |
> | amy333    |
> | dan456    |

Upvotes: 1

Nate
Nate

Reputation: 67

You need to do an INNER JOIN of the two tables on the actNumber column. This will produce a result set containing only actNumber records that exist in both table 1 and table 2.

For example,

SELECT TABLE_1.loginName FROM TABLE_1 AS tbl1 INNER JOIN TABLE_2 AS tbl2 ON tbl1.actNumber = tbl2.actNumber

Upvotes: 1

Related Questions