Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    7

    Unanswered: How to make join match NULL to NULL and more

    Hello everyone

    I have 2 tables_

    TABLEA

    acolid_____acol1____acol2
    __1________abc_______pqr
    _NULL______def_______lmn
    _ ________ghi_______stv

    TABLEB

    bcolid_____bcol1_____bcol2___bcol3
    __1________lmnop_____wxyz____345
    _NULL______defg______qrstu___567
    _ ________vuxy______tptt____1234


    The 3rd row of 1st column on each table contains '' (not a NULL) value.

    If I try to see the datalenght of this column, I get 0 (zero) for that row.

    Lets say I want to do a query like following

    Code:
    select acol2, bcol3 from TABLEA, TABLEB
    where acolid = bcolid

    Right now I am getting:

    acol2__________bcol3
    pqr_____________345

    What I actually want to see is:

    acol2__________bcol3
    pqr_____________345
    lmn_____________567
    stv_____________1234


    In other words I want all the rows to be picked up in the query regardless. That is I want the join also to match the NULL to NULL and '' to ''.

    Can this be done? Is there a way to get that query?

    Any recomendations, suggestions greatly appreciated.
    Last edited by edyl; 05-03-11 at 11:48.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select acol2, bcol3 
    from TABLEA, TABLEB
    where COALESCE(acolid, -1) = COALESCE(bcolid, -1)
    The -1 value should be something that will never occur in your table. When there is nu such value , you can always use:
    Code:
    select acol2, bcol3 
    from TABLEA, TABLEB
    where acolid = bcolid OR
         (acolid IS NULL AND bcolid IS NULL)
    Or use a UNION instead of the OR.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM table1
       JOIN table2
          ON (table1.PrimaryKey = table2.PrimaryKey
          OR (table1.PrimaryKey IS NULL and table2.PrimaryKey IS NULL))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2011
    Posts
    7
    Thank you very much guys.
    Last edited by edyl; 05-03-11 at 17:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •