get rows from joins which doesn't match

  • SELECT EP.EMPLOYEE_ID, EP.FIRST_NAME + ' ' + ISNULL(EP.MIDDLE_NAME, ' ') + ' ' + ISNULL(EP.LAST_NAME, ' ') AS FULL_NAME, MB.BUSINESSUNIT_NAME,

    CONVERT(VARCHAR(10), EP.DATE_OF_BIRTH, 101) AS DATE_OF_BIRTH, EP.MOBILE, CONVERT(VARCHAR(10), ECM.RECRUITMENT_DATE, 101) AS JOINING_DATE,

    EC.PRESENT_ADDRESS_LINE_1, EC.PRESENT_ADDRESS_LINE_2, EC.PRESENT_ADDRESS_LINE_3

    FROM EIS_PERSONAL EP LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID

    LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID

    LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID

    RIGHT JOIN EMPLOYEE_RESIGNATION ER ON ER.TRANSFER_EMP_ID = EP.EMPLOYEE_ID

    RIGHT JOIN EMPLOYEE_TERMINATION ET ON ET.TRANSFER_EMP_ID = EP.EMPLOYEE_ID

    WHERE ECM.BUSINESSUNIT_ID != 12

    ORDER BY ECM.BUSINESSUNIT_ID

    In the above query, there are some employee ID's in EMPLOYEE_RESIGNATION and EMPLOYEE_TERMINATION table and i m match it with other table as shown above.

    I want to eliminate details that match ID's with the above mentioned 2 table.

    How do i do this.

    Thank u all

  • Do you want to eliminate the IDs in the result set that are in EMPLOYEE_RESIGNATION or EMPLOYEE_TERMINATION? is that what you want?

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • yeah atif that is want i wanted to do. There are almost 100 rows and i m deleting manually.

    If u can help me it will be a great help.

  • Hey there, you can use on of the following

    SELECT

    EP.EMPLOYEE_ID,

    EP.FIRST_NAME + ' ' + ISNULL(EP.MIDDLE_NAME, ' ') + ' ' + ISNULL(EP.LAST_NAME, ' ') AS FULL_NAME,

    MB.BUSINESSUNIT_NAME,

    CONVERT(VARCHAR(10), EP.DATE_OF_BIRTH, 101) AS DATE_OF_BIRTH,

    EP.MOBILE, CONVERT(VARCHAR(10), ECM.RECRUITMENT_DATE, 101) AS JOINING_DATE,

    EC.PRESENT_ADDRESS_LINE_1,

    EC.PRESENT_ADDRESS_LINE_2,

    EC.PRESENT_ADDRESS_LINE_3

    FROM

    EIS_PERSONAL EP

    LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID

    LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID

    LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID

    --=== NEW ADDITION

    LEFT JOIN / RIGHT / INNER -- Remove whichever join u dont need

    ( SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_RESIGNATION

    UNION

    SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_TERMINATION

    ) A

    ON A.EMP_ID = EP.EMPLOYEE_ID

    WHERE

    ECM.BUSINESSUNIT_ID != 12

    ORDER BY

    ECM.BUSINESSUNIT_ID

    or

    SELECT

    EP.EMPLOYEE_ID,

    EP.FIRST_NAME + ' ' + ISNULL(EP.MIDDLE_NAME, ' ') + ' ' + ISNULL(EP.LAST_NAME, ' ') AS FULL_NAME,

    MB.BUSINESSUNIT_NAME,

    CONVERT(VARCHAR(10), EP.DATE_OF_BIRTH, 101) AS DATE_OF_BIRTH,

    EP.MOBILE, CONVERT(VARCHAR(10), ECM.RECRUITMENT_DATE, 101) AS JOINING_DATE,

    EC.PRESENT_ADDRESS_LINE_1,

    EC.PRESENT_ADDRESS_LINE_2,

    EC.PRESENT_ADDRESS_LINE_3

    FROM

    EIS_PERSONAL EP

    LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID

    LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID

    LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID

    --=== NEW ADDITION

    WHERE

    ECM.BUSINESSUNIT_ID != 12

    AND EP.EMPLOYEE_ID IN

    (

    SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_RESIGNATION

    UNION

    SELECT DISTINCT TRANSFER_EMP_ID EMP_ID FROM EMPLOYEE_TERMINATION

    )

    ORDER BY

    ECM.BUSINESSUNIT_ID

    Hope this helps you!

    Cheers!

  • sachinrshetty (5/12/2010)


    FROM EIS_PERSONAL EP LEFT JOIN EIS_CONTACTS EC ON EP.EMPLOYEE_ID = EC.EMPLOYEE_ID

    LEFT JOIN EIS_COMPANY ECM ON EP.EMPLOYEE_ID = ECM.EMPLOYEE_ID

    LEFT JOIN MASTER_BUSINESSUNIT MB ON ECM.BUSINESSUNIT_ID = MB.BUSINESSUNIT_ID

    RIGHT JOIN EMPLOYEE_RESIGNATION ER ON ER.TRANSFER_EMP_ID = EP.EMPLOYEE_ID

    RIGHT JOIN EMPLOYEE_TERMINATION ET ON ET.TRANSFER_EMP_ID = EP.EMPLOYEE_ID

    In the above query, there are some employee ID's in EMPLOYEE_RESIGNATION and EMPLOYEE_TERMINATION table and i m match it with other table as shown above.

    If you eliminate rows that match one of those two tables, I think you'll eliminate everything as you've RIGHT joined to them, meaning (correct me if I'm wrong) that you will ONLY be showing rows currently that appear in one or both of these tables?

    I think you need to use a LEFT join with an 'IS NULL' condition in the where clause, AKA an 'anti' join.

    My example :

    /* SETUP */

    declare @emp table (empId int, empName varchar(15))

    declare @empTerm table (empId int, empTermDate datetime)

    declare @empResig table (empId int, empResigDate datetime)

    insert into @emp (empId, empName) values (1, 'Kev')

    insert into @emp (empId, empName) values (2, 'Jeff Quit')

    insert into @emp (empId, empName) values (3, 'Bob')

    insert into @emp (empId, empName) values (4, 'Dave Sacked')

    insert into @emp (empId, empName) values (5, 'Ian')

    insert into @emp (empId, empName) values (6, 'Harry Redundant')

    insert into @empResig(empId, empResigDate) values (2, GETDATE()-30)

    insert into @empTerm(empId, empTermDate) values (4, GETDATE()-60)

    insert into @empTerm(empId, empTermDate) values (6, GETDATE()-90)

    /* QUERIES */

    select 'Term Only', * from @emp e RIGHT JOIN @empTerm et ON e.empId = et.empId

    select 'Resig Only', * from @emp e RIGHT JOIN @empResig er ON e.empId = er.empId

    select 'Your Query', * from @emp e RIGHT JOIN @empTerm et ON e.empId = et.empId RIGHT JOIN @empResig er ON e.empId = er.empId

    select 'My Query', * from @emp e

    LEFT JOIN @empTerm et ON e.empId = et.empId

    LEFT JOIN @empResig er ON e.empId = er.empId

    WHERE et.empId IS NULL AND er.empId IS NULL

    -------------------------------Oh no!

  • Thank u Kevin and coldcoffee for your help. I will try your logic and revert back to u.

    Thanks a lot.

    Sachin

  • It should be added at this point that ColdCoffee's solutions produce the set you wish to exclude (i.e. those that ARE in one of those tables) whereas mine is intended to adjust your original query such that it will return all rows which are NOT in one of those tables and then you would skip the deletion stage.

    Take your pick of what you were trying to achieve.

    -- Kev

    -------------------------------Oh no!

  • May be of interest here - http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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