[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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!
0
bbdesign
Asked:
bbdesign
2 Solutions
 
Paul MacDonaldDirector, Information SystemsCommented:
Select whatever From tbl2 Where userid Not In
 (Select userid From tbl1)

Or something to that effect.
0
 
BartVxCommented:
You can do this in a number of ways, for example:

SELECT t1.UserID FROM Table1 t1 WHERE NOT EXISTS (SELECT t2.UserID FROM Table2 t2 WHERE t2.UserID = t1.UserID)



SELECT t1.UserID FROM Table1 t1
EXCEPT
SELECT t2.UserID FROM Table2 t2
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Hello, I'd first select all unique user id's from tbl2 and then select the userid's from tbl1 that cannot be found in the first selection, like this:
select userid from tbl1
where userid not in 
(
  select distinct userid from tbl2
);

Open in new window

0
 
bbdesignAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now