Post reply

Find unmatched between 2 tables using field as selection criteria

  • January 21, 2021 at 5:36 am

    Go to Answer

    #3835752

    I have the following tables as a example.

    Table 1

    EmpID, LastName, FirstName

    1,Smith,John

    2,Jones,Bob

    3,Citizen,Jane

    Table 2

    EmpID,ReqID,ReqDesc

    1,1,Car

    1,2,Diploma

    1,3,Phone

    2,1,Car

    2,3,Phone

    3,1,Car

    3,2,Diploma

    3,3,Phone

    I want to return all records from Table 1 that don't have an entry in Table 2 that don't have a Diploma for example so ReqID = 2

    This query gives me all records back though not just Bob Jones' record. As they have other records that ReqID <> 2. Is there some way to group them to achieve this?

    SELECT Table1.EMPID,
    FROM Table1 LEFT JOIN Table2 ON Table1.EMPID = Table2.EMPID
    WHERE Table2.ReqID <> 2;

  • January 21, 2021 at 6:06 am

    Answer

    #3835764

    I'm too lazy to create tables to test.  But I believe that there are 2 ways that you could achieve this.

    SELECT    Table1.EMPID
    FROM      Table1
    LEFT JOIN Table2
           ON Table1.EMPID = Table2.EMPID
          AND Table2.ReqID = 2
    WHERE     Table2.EMPID IS NULL;
    SELECT  Table1.EMPID
    FROM    Table1
    WHERE   NOT EXISTS (
                         SELECT 1
                         FROM   Table2
                         WHERE  Table1.EMPID = Table2.EMPID
                           AND  Table2.ReqID = 2
                       );

    How to post data/code on a forum to get the best help.

  • January 21, 2021 at 11:46 pm

    #3836074

    Thanks for this. I know it would be something rather straight forward but I just couldn't get it.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply