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

Posted on 2011-10-12
Last Modified: 2012-05-12
If users in a database are identified by ID number, lets say there are two tables:


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!
Question by:bbdesign
    LVL 33

    Expert Comment

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

    Or something to that effect.
    LVL 3

    Accepted Solution

    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
    SELECT t2.UserID FROM Table2 t2
    LVL 37

    Assisted Solution

    by:Gerwin Jansen
    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


    Author Comment


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now