Pull record between 2 columns-CodePudding

I have 2 tables

table1

ID    VendorID    
100   11190       
200   99999

table2

ID    VendorID     
100   11190          
100   11190           
200   12523        
200   53266

My expect result and my goal if ID and VendorID from table1 match with ID and VendorID from table2 then flag NO

table1ID    table1Vendor    table2ID    table2Vendor    Code
100         12345           100         12345           No
100         12345           100         12345           No
100         12345           100         45678           No
200         56489           200         11111           Use
200         56489           200         22222           Use

My query

SELECT a.id as table1ID, a.vendorid as table1Vendor, b.id as table2ID, b.Vendorid as table2Vendor
    , case
    when a.vendorid <> b.Vendorid
    then 'Use' else 'No'
    end as Code
FROM table_1 a
JOIN table_2 b  on a.id = b.id

But I got

table1ID    table1Vendor    table2ID    table2Vendor    Code
100         12345           100         12345           No
100         12345           100         12345           No
100         12345           100         45678           Use
200         56489           200         11111           Use
200         56489           200         22222           Use

You can see row 3 is incorrect, should code as NO cause 12345(table1vendor) still match with 12345(table2vendor)

Not sure why, need some help. Thank you.

CodePudding user response：

You are only checking the same row for a match, but according to your logic you need to check all rows, therefore you need another sub-query.

SELECT a.ID AS table1ID, a.VendorID AS table1Vendor, b.ID AS table2ID, b.VendorID as table2Vendor
  , CASE WHEN EXISTS (SELECT 1 FROM table_2 b1 WHERE b1.id = a.ID AND b1.VendorID = a.VendorID) THEN 'No' ELSE 'Use' END AS Code
FROM table_1 a
JOIN table_2 b ON a.ID = b.ID;