CODEWITHSUNDEEP
sqlsql-server
new guy
new guy

Reputation: 149

Find records from one table which don't exist in another & users mapped from third table

I'm new to SQL & trying my hands-on.

Looking for an efficient way to get users (*) which are present in user table but not present in temp table

Have three tables:

Temp:

member_no | name

User:

endUserId | name 

Login:

member_no | endUserId

I tried this but not working:

SELECT * 
FROM User LEFT OUTER JOIN
     Temp
      ON User.endUserId = (SELECT TOP 1 e.endUserId
                           FROM User e JOIN
                                LOGIN l
                                ON e.endUserId = l.endUserId 
                           WHERE l.username = Temp.member_no
                          )

Thanks in advance

Upvotes: 1

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If you want users that are not in temp, then not exists or not in come to mind:

select u.*
from user u
where not exists (select 1
                  from temp t join
                       login l
                       on t.memberno = l.memberno
                  where l.endUserId = u.endUserId
                 );

Upvotes: 2

Related Questions