SQL Server Performance

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

Discussion in 'General Developer Questions' started by pkarunit, Jun 10, 2008.

  1. pkarunit New Member



    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

  2. SQL2000DBA New Member

    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
  3. pkarunit New Member

    Hi Hemant ,
    This wont work.. and I have tried
  4. John Hanrahan New Member

    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
  5. Adriaan New Member

    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))

Share This Page