SQL - no output from Right Join

Question

I have one table which I select with the code: Code A

Select TDS, TL, IK
From (Select Sheet1.TOOLING_DATA_SHEET As TDS, Sheet1.CUTTING_TOOL As TL, ENT_ITEM_MASTER.ITEM_KEY As IK
        From Sheet1
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_CODE=Sheet1.CUTTING_TOOL And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As A

Output:

TDS                     TL          IK
TDS-1980D-10+OP10+S7    TL-000032   1
TDS-1980D-10+OP10+S7    TL-000019   34
TDS-2258-01+OP10+S4     TL-000016   53
TDS-2325PU+OP10+S1      TL-000036   7
TDS-1234-56-78          TL-000123   45

and another table which I select with the code: Code B

Select ENT_LINK_OBJECTS.OBJ_NAME, ENT_ITEM_MASTER.ITEM_CODE, ENT_ITEM_MASTER.ITEM_KEY
        From ENT_LINK_OBJECTS
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_KEY=ENT_LINK_OBJECTS.ENTITY_KEY And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As B

Output:

OBJ_NAME                 ITEM_CODE   ITEM_KEY
TDS-1980D-10+OP10+S7    TL-000032      1
TDS-1980D-10+OP10+S7    TL-000019      34
TDS-2258-01+OP10+S4     TL-000032      28
TDS-2258-01+OP10+S4     TL-000016      53
TDS-2325PU+OP10+S1      TL-000036      7
TDS-2325PU+OP10+S1      TL-000009      9

I have Left Joined the tables in working code which gives me everything that is in Table A that is not in Table B.

I now am trying to Right Join the tables which would give me everything that is in Table B that is not in Table A. Right now the output is nothing.

---

Full code for Right Join:

Select TDS, TL, IK
From (Select Sheet1.TOOLING_DATA_SHEET, Sheet1.CUTTING_TOOL, ENT_ITEM_MASTER.ITEM_KEY
        From Sheet1
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_CODE=Sheet1.CUTTING_TOOL And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As A
Right Join (Select ENT_LINK_OBJECTS.OBJ_NAME As TDS, ENT_ITEM_MASTER.ITEM_CODE As TL, ENT_ITEM_MASTER.ITEM_KEY As IK
        From ENT_LINK_OBJECTS
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_KEY=ENT_LINK_OBJECTS.ENTITY_KEY And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As B
On A.TOOLING_DATA_SHEET=B.TDS
Where A.TOOLING_DATA_SHEET is Null

Current output:

TDS    TL    IK

Desired output: (everything in B that is not in A)

TDS                       TL           IK
TDS-2258-01+OP10+S4     TL-000032      28
TDS-2325PU+OP10+S1      TL-000009      9

I can add my working code for my Left Join if that is helpful. I have also tried switching the Select areas, doing a Left Join, and changing the Null statement to attempt a Left Join, which provides no output as well.

EDIT:

How my code works with a Left Join:

Select TDS, TL, IK
From (Select Sheet1.TOOLING_DATA_SHEET As TDS, Sheet1.CUTTING_TOOL As TL, ENT_ITEM_MASTER.ITEM_KEY As IK
        From Sheet1
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_CODE=Sheet1.CUTTING_TOOL And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As A
Left Join (Select ENT_LINK_OBJECTS.OBJ_NAME, ENT_ITEM_MASTER.ITEM_CODE, ENT_ITEM_MASTER.ITEM_KEY
        From ENT_LINK_OBJECTS
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_KEY=ENT_LINK_OBJECTS.ENTITY_KEY And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As B
On A.TDS=B.OBJ_NAME
Where B.OBJ_NAME is Null

OUTPUT: (everything in A that is not in B)

TDS                  TL        IK
TDS-1234-56-78     TL-000123   45

Answer 1

---You have two problems

1. Since you want all records from B not in A. You need to display B table Columns
2. Your Join criteria doesn't quantify unique records. 28 and 9 are being removed because TDS-2258-01+OP10+S4 and TDS-2325PU+OP10+S1 do exist in table A. The issue is TDS-2258-01+OP10+S4 TL-000032 doesn't exist in A nor does TDS-2325PU+OP10+S1 TL-000009. The criteria you're using to JOIN on is incorrect. To know the CORRECT values you need to specify the relationship between the tables or simply (based on displayed data) use On A.TDS=B.OBJ_NAME and A.TL = B.Item_Code and A.IK = B.Item_key

Meaning final result would be:

Select B.TDS, B.TL, B.IK
From (Select Sheet1.TOOLING_DATA_SHEET, Sheet1.CUTTING_TOOL, ENT_ITEM_MASTER.ITEM_KEY
        From Sheet1
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_CODE=Sheet1.CUTTING_TOOL And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As A
Right Join (Select ENT_LINK_OBJECTS.OBJ_NAME As TDS, ENT_ITEM_MASTER.ITEM_CODE As TL, ENT_ITEM_MASTER.ITEM_KEY As IK
        From ENT_LINK_OBJECTS
        Inner Join ENT_ITEM_MASTER
        On ENT_ITEM_MASTER.ITEM_KEY=ENT_LINK_OBJECTS.ENTITY_KEY And ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As B
On A.TDS=B.OBJ_NAME
and A.TL = B.Item_Code
and A.IK = B.Item_ke
Where A.TOOLING_DATA_SHEET is Null

If you're RDBMS supports MINUS (EXCEPT for SQL SERVER) this would also work

(SELECT ENT_LINK_OBJECTS.OBJ_NAME As TDS, 
   ENT_ITEM_MASTER.ITEM_CODE As TL, 
   ENT_ITEM_MASTER.ITEM_KEY As IK
 FROM ENT_LINK_OBJECTS
 INNER JOIN ENT_ITEM_MASTER
   ON ENT_ITEM_MASTER.ITEM_KEY=ENT_LINK_OBJECTS.ENTITY_KEY 
  AND ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null) As B
EXCEPT 
(SELECT Sheet1.TOOLING_DATA_SHEET, 
        Sheet1.CUTTING_TOOL, 
        ENT_ITEM_MASTER.ITEM_KEY
 FROM Sheet1
 INNER JOIN ENT_ITEM_MASTER
   ON ENT_ITEM_MASTER.ITEM_CODE=Sheet1.CUTTING_TOOL 
  AND ENT_ITEM_MASTER.USER_LAST_MODIFIED Is Not Null)  A

It basically says take result set B and subtract from it Result Set A. which leaves you with.... the two records you're after.. This only works if all columns match. Outer joins or exists/not exists provide greater flexibility.

Visual Aid on Joins to help better understand