Link to home
Start Free TrialLog in
Avatar of Brad Bansner
Brad Bansner

asked on

how to check for record NOT existing in another table by user ID number?

If users in a database are identified by ID number, lets say there are two tables:

tbl1.userid
tbl2.userid

tbl1 is the main table -- every user in the system has a single record (userid is unique)

tbl2 contains info about the user saved at various times -- every user could have many records or they could have zero records (many records in this table with the same userid)

How could I figure out which tbl1.userid is MISSING a record in tbl2? In other words, I want to select all userid's where nothing has ever been entered in tbl2 for the user.

I am currently doing this with ASP scripting but it is inefficient that way, because I have to loop through thousands of userid's and then do a 2nd query to check against tbl2. I'm guessing there is a better way.

Thank you!
Avatar of Paul MacDonald
Paul MacDonald
Flag of United States of America image

Select whatever From tbl2 Where userid Not In
 (Select userid From tbl1)

Or something to that effect.
ASKER CERTIFIED SOLUTION
Avatar of BartVx
BartVx
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brad Bansner
Brad Bansner

ASKER

Thanks!