CODEWITHSUNDEEP
mysql
Sideshow Bob
Sideshow Bob

Reputation: 4716

mysql join with precedence

Is it possible to make a join in mysql so that the values from the right table are returned if and only if the corresponding values in the left table don't exist?

For example

SELECT * FROM
    (SELECT userid,status FROM date_status WHERE date='20-03-2012') ds
JOIN 
    (SELECT userid,status FROM default_weekday_status WHERE day='tuesday') dws
ON
    ds.userid=dws.userid

I would like this to return date_status.status if the row in date_status exists, but default_weekday_status.status otherwise.

(Note that 20-03-2012 is a Tuesday and no, tuesday wouldn't be stored as a string ;-) )

Upvotes: 0

Views: 467

Answers (2)

fie
fie

Reputation: 416

The easiest way to do this is with the COALESCE function. I will return the first not-null value in the list of parameters.

SELECT coalesce(ds.status,dws.status) as status FROM
    date_status ds
RIGHT JOIN 
    default_weekday_status dws
ON
    ds.userid=dws.userid
WHERE
    ds.date='20-03-2012' or dws.day='tuesday'

The sub-queries have been removed, as they aren't very efficient.

MySQL - COALESCE

Upvotes: 3

Konerak
Konerak

Reputation: 39763

Use a RIGHT OUTER JOIN to select all the values from the right table, even if there is no value for the left table.

Now use COALESCE to get the ds.value, and when the value is NULL, get the dws.value:

SELECT COALESCE(ds.value,dws.value) FROM
    (SELECT userid,status FROM date_status WHERE date='20-03-2012') ds
RIGHT OUTER JOIN 
    (SELECT userid,status FROM default_weekday_status WHERE day='tuesday') dws
ON
    ds.userid=dws.userid

P.S.: a RIGHT OUTER JOIN is just a LEFT JOIN with the tables in the inverse order...

Upvotes: 3

Related Questions