Find unmatched between 2 tables using field as selection criteria

  • 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;
  • 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
    );
  • 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