Join on NULL Column values

sunfire · #1 (**permalink**) 05-07-10, 15:42

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!

r937 · #2 (**permalink**) 05-07-10, 15:47

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

sunfire · #3 (**permalink**) 05-07-10, 15:49

sorry, the 'yyy222' is coming from the other table called "other_patches"

Thanks!

r937 · #4 (**permalink**) 05-07-10, 15:53

Quote:

Originally Posted by sunfire

sorry, the 'yyy222' is coming from the other table called "other_patches"

and how does this happen?

sunfire · #5 (**permalink**) 05-07-10, 15:57

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

r937 · #6 (**permalink**) 05-07-10, 16:01

Quote:

Originally Posted by sunfire

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

sunfire · #7 (**permalink**) 05-07-10, 16:14

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

r937 · #8 (**permalink**) 05-07-10, 16:34

Quote:

Originally Posted by sunfire

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

sunfire · #9 (**permalink**) 05-07-10, 16:47

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!

r937 · #10 (**permalink**) 05-07-10, 16:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech