sql join on null value …

I had to join tables x and y based on three attributes a, b and c. The table y was made to link a specific id with each triplet (a,b,c). Some of the definition of the id’s had a c parameter that was NULL… Thus, the join did not work.

The dirty workaround was the following : a,b and c where only positive integer (except the NULL values). We updated the y table setting NULL to -1. Then in the join query we used the ifnull() mysql function :

SELECT x.id, y.id, x.a, x.b, x.c FROM x
RIGHT OUTER JOIN y ON x.a = y.a
AND x.b = y.b
AND ifnull(x.c, -1) = y.c

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: