1

I'm trying to join 2 different tables (Table99 and Table2) to Table1. The first is a straight-forward RIGHT JOIN. The second is a LEFT JOIN, where I'm trying to take Table1 and Left Join to a Table2, and bring back results from Table1 that don't exist in Table2

Example:

 FROM Table1 tbl1 
      RIGHT JOIN Table99 tbl99 ON tbl1.CARD_ID = tbl99.CARD_ID 
            AND tbl1.DATE_OPEN = tbl99.DATE_OPEN 

      LEFT JOIN Table2 tbl2 ON tbl1.ACCOUNT_NUMBER = tbl2.ACCOUNT_NUMBER 
           AND tbl1.APPL_ID = tbl2.APPL_ID'

WHERE tbl1.ACCOUNT_NUMBER <> tbl2.ACCOUNT_NUMBER 

The Left Join clause is where I'm trying to pull in all of the results from tble1 that do NOT exist in tbl2

  • NOT EXISTS also works in several flavors of sql that could work to get what you need. Also it would help to know how many tables you are trying to join and what tables you need data from as that will impact what you are trying to do. – Joe W Feb 7 at 19:56
  • SQL Server 2012 – Mike Jones Feb 7 at 20:04
3

bring back results from Table1 that don't exist in Table2

You can use NOT EXISTS

SELECT
    T1.*
FROM
    TABLE1 T1
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER 
                        AND T2.APPL_ID = T1.APPL_ID );     

Update

It doesn't matter how many JOIN's you use before the left anti semi join, it returns all rows from the left side that does not exists on the right side, according the predicate: WHERE T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER AND T2.APPL_ID = T1.APPL_ID

SELECT
    T1.*
FROM
    TABLE1 T1
RIGHT JOIN 
    TABLE99 T99
    ON T99.CARD_ID = T99.CARD_ID 
    AND T99.DATE_OPEN = T1.DATE_OPEN
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
                        AND  T2.APPL_ID = T1.APPL_ID
                );

Let's imagine 3 tables with next ID's:

 T1    T2    T9
----  ----  ----
 01    01    01
 02          02
 03    03
 04    04    04
 05          05

Give me all ID's that exists in T1 and T9.

SELECT ID FROM T1 JOIN T9 ON T1.ID = T2.ID;

01,02,04,05

and now, give me all ID's of this group that does not exists in T2:

pseudo-code:

SELECT ID FROM (01,02,04,05) WHERE NOT EXISTS IN (01,03,04);

02,05

NOTE: I've replaced table names by their values to make the final result more clear

  • I've seen the "NOT EXISTS" before, but I'm just not exactly sure how to use it here because I actually have 3 tables that I'm joining and it only looked like the NOT EXISTS worked when just using 2 tables. 'FROM Table1 tbl1 RIGHT JOIN Table99 tbl99 ON tbl1.CARD_ID = tbl99.CARD_ID AND tbl1.DATE_OPEN = tbl99.DATE_OPEN LEFT JOIN Table2 tbl2 ON tbl1.ACCOUNT_NUMBER <> tbl2.ACCOUNT_NUMBER AND tbl1.APPL_ID <> tbl2.APPL_ID' – Mike Jones Feb 7 at 19:21
  • I think I've almost got it, but I'm not sure where I would add in a WHERE clause for the RIGHT JOIN part. For example, if I were to add in "WHERE tbl1.STATUS_CODE IN(1,2)" – Mike Jones Feb 7 at 22:17
  • @MikeJones what do you mean "you almost got it"? The answer by McNets seems to be 100% correct and you can use it as it is. Do you want to change it? – ypercubeᵀᴹ Feb 7 at 22:37
  • SELECT T1.* FROM TABLE1 T1 RIGHT JOIN TABLE99 T99 ON T99.CARD_ID = T99.CARD_ID AND T99.DATE_OPEN = T1.DATE_OPEN WHERE tbl1.STATUS_CODE IN(1,2) WHERE NOT EXISTS(SELECT 1 FROM TABLE2 T2 WHERE T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER AND T2.APPL_ID = T1.APPL_ID ); – Mike Jones Feb 7 at 22:38
  • If you want to add more filters (on T1) then that should be in the (RIGHT JOIN) ON clause: SELECT T1.* FROM TABLE1 T1 RIGHT JOIN TABLE99 T99 ON T99.CARD_ID = T99.CARD_ID AND T99.DATE_OPEN = T1.DATE_OPEN AND T1.STATUS_CODE IN(1,2) WHERE NOT EXISTS(SELECT 1 FROM TABLE2 T2 WHERE T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER AND T2.APPL_ID = T1.APPL_ID ); – ypercubeᵀᴹ Feb 7 at 22:41
0

Add the following to your where clause.

AND tbl2.APPL_ID IS NULL

  • This does not seem like the best way to do that. – Joe W Feb 7 at 19:53
  • But it will work, and it is simple, maintainable, and deterministic. – John Rennpferd Feb 8 at 20:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.