A tricky question and i do not how can I resolve in joins please help me

TABLE A



BRANCH_ID

ADDRESS_NUMBER

ADDRESS_SHIP_TO

PRODUCT_ID_KEY

ISO_LANGUAGE

1561001

NULL

NULL

10005899

en-US

1561001

NULL

NULL

10005899

nl-NL

1561001

35023080

NULL

10005899

en-US

1561001

35023080

35023080

10005899

en-US



TABLE B



BRANCH_ID

ADDRESS_NUMBER

ADDRESS_SHIP_TO

PRODUCT_ID_KEY

ISO_LANGUAGE

1561001

8000

9000

10005899

en-US

1561001

8000

9000

10005899

nl-NL

1561001

35023080

1250

10005899

en-US

1561001

35023080

35023080

10005899

en-US
If I join the table A with table I am expecting the below result, In the sense if the column exactly match thenit should bring the particular record else check for the null record . itsjust like priority of column handling … Is this possible in the single query ? please let me know.

1561001

NULL

NULL

10005899

en-US

1561001

NULL

NULL

10005899

nl-NL

1561001

35023080

NULL

10005899

en-US

1561001

35023080

35023080

10005899

en-US

please try this

SELECT A.BRANCH_ID,A.ADDRESS_NUMBER,A.ADDRESS_SHIP_TO,A.PRODUCT_ID_KEY,A.ISO_LANGUAGE
FROM TABLEA A LEFT OUTER JOIN TABLEB B
ON A.BRANCH_ID=B.BRANCH_ID
AND A.ADDRESS_NUMBER=B.ADDRESS_NUMBER
AND A.ADDRESS_SHIP_TO=B.ADDRESS_SHIP_TO
AND A.PRODUCT_ID_KEY=B.PRODUCT_ID_KEY
AND A.ISO_LANGUAGE=B.ISO_LANGUAGE

Hi Hemant ,
This wont work.. and I have tried

This works though I'm not sure it will always return what you want (replace the @_A and @_B w/ appropriate table name):SELECT DISTINCT a.BRANCH_ID, a.ADDRESS_NUMBER, a.ADDRESS_SHIP_TO, a.PRODUCT_ID_KEY, a.ISO_LANGUAGE
FROM @_A aLEFT OUTER JOIN @_B b
ON a.BRANCH_ID=b.BRANCH_IDAND A.ADDRESS_NUMBER=B.ADDRESS_NUMBER
AND A.ADDRESS_SHIP_TO=B.ADDRESS_SHIP_TOAND A.PRODUCT_ID_KEY=B.PRODUCT_ID_KEY
AND A.ISO_LANGUAGE=B.ISO_LANGUAGE

You know the part of the join that must always be true - the first column. You handle the second column outside of the join, in the WHERE clause:
SELECT A.col1, B.col3
FROM A
INNER JOIN B ON A.col1 = B.col1
WHERE (A.col2 = B.col2)
OR ((B.col2 IS NULL) AND
NOT EXISTS (SELECT x.* FROM B x WHERE x.col1 = A.col1 AND x.col2 = A.col2))