Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    19

    Unanswered: Join on NULL Column values

    Hello,
    How do I do a Join on a column where the value of that column is null? I have that value in another table called "other_patches" and the common column between the two is patch.

    So I have a table called patches

    rid, date, name, patch, patchid
    1,2010-1-1,foo,foo-1,xxx111
    2,2010-1-1,foo2,foo-2,[NULL]

    Want it to look like
    rid, date, name, patch, patchid
    1,2010-1-1,foo,foo-1,xxx111
    2,2010-1-1,foo2,foo-2,yyy222

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you repeat the question please?

    a join usually involves more than one table (unless it's a self-join)

    i don't understand where the value yyy222 comes from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Posts
    19
    sorry, the 'yyy222' is coming from the other table called "other_patches"

    Thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunfire View Post
    sorry, the 'yyy222' is coming from the other table called "other_patches"
    and how does this happen?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2003
    Posts
    19
    Ok, I'm going to re-word this :-)

    I have two tables (patches, other_patches) and I need to join the two based on the "patch" column but only if the "patchid" column is NULL.

    Thanks!

    Example Tables:

    patches
    rid, date, name, patch, patchid
    1,2010-1-1,foo,foo-1,xxx111
    2,2010-1-1,foo2,foo-2,[NULL]

    other_patches
    rid, date, name, patch, patchid
    1,2010-1-1,foo2,foo-2,yyy222

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunfire View Post
    ... join the two based on the "patch" column but only if the "patchid" column is NULL.
    starting to make a bit more sense now, but i have a feeling this still isn't really what you want
    Code:
    SELECT patches.rid 
         , patches.date 
         , patches.name 
         , patches.patch 
         , other_patches.patchid
      FROM patches
    INNER
      JOIN other_patches
        ON other_patches.patch = patches.patch
     WHERE patches.patchid IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2003
    Posts
    19
    Your right, I tried this, but it's filtering out all of the patches.

    I know my syntax is wrong here :-) but It's the only way I can think of to describe it.

    Code:
    SELECT patches.rid 
         , patches.date 
         , patches.name 
         , patches.patch 
         , patches.patchid
         , other_patches.patchid
      FROM patches
    IFNULL(patches.patchid,
    INNER
      JOIN other_patches
        ON other_patches.patch = patches.patch
     WHERE patches.patchid IS NULL)
    This would populate that null value. To kind of look like this...

    Results
    rid, date, name, patch, patchid
    1,2010-1-1,foo,foo-1,xxx111
    2,2010-1-1,foo2,foo-2,yyy222

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunfire View Post
    Your right, I tried this, but it's filtering out all of the patches.
    based on the fact that you said you only wanted to join them if the patchid is null

    honest, that's what you said

    okay, let me take another wild guess...
    Code:
    SELECT patches.rid 
         , patches.date 
         , patches.name 
         , patches.patchid
         , COALESCE(other_patches.patchid,patches.patchid) AS patchid
      FROM patches
    LEFT OUTER
      JOIN other_patches
        ON other_patches.patch = patches.patch
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2003
    Posts
    19
    This is great, the COALESCE was the command I was looking for...

    Here is how I solved it

    SELECT patches.rid
    , patches.date
    , patches.name
    , COALESCE(patches.patchid,
    (select other_patches.patchid from other_patches
    Where other_patches.name = patches.name)) as patchid
    FROM patches


    Thanks for all of your help!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you sure solved it all right

    bookmark this thread as you will also want to know how to use a LEFT OUTER JOIN some day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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