SQL - LEFT JOIN multiple conditions - priority

left-join, mysql, sql

Solution

You can use `COALESCE()` and join to your address table twice:

  SELECT user.id
       ,COALESCE(home.address, office.address) AS Address
  FROM user 
  LEFT JOIN user_address AS home
     ON user.id = home.user_id 
       AND home.type = "home"
  LEFT JOIN user_address AS office
     ON user.active_office_address_id = office.user_id 
  GROUP BY user.id

Problem

I have 2 tables with a structure similar with this: table: user fields: id, active_office_address_id (this can be 0) table: user_address fields: id, user_id, type (home, office) A user can have a "home" address (not mandatory) and multiple "office" addresses. I have a join to get a user address, but I want that if the user have a "home" address to get that address, not "office" address. So, how can I get "home" address if exists, and only if that not exists to get "office" address. (In reality the query is much more complicated and the join is done on 4-5 tables) ``` SELECT * FROM user LEFT JOIN user_address ON (user.id = address.user_id AND (user_address.type = "home" OR user.active_office_address_id = user_address.id)) group by user.id ```

Original source