SQL - LEFT JOIN multiple conditions - priority

Question

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

Answer 1

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

Answer 2

Two left joins and a case statement will give you the address id you want.

SELECT user.*,CASE WHEN home_addr.id IS NOT NULL THEN home_addr.id ELSE ofc_addr.id END AS addr_id
FROM user
LEFT JOIN user_address AS home_addr
  ON (user.id = home_addr.user_id AND home_addr.type = 'home')
LEFT JOIN user_address AS ofc_addr
  ON (user.active_office_address_id = ofc_addr.id)

You could feed this back in as a sub-select for a particular user:

SELECT * FROM user LEFT JOIN user_address
WHERE user.id = ?
AND user_address.user_id = user.id
AND user_address.id IN
    (SELECT CASE WHEN home_addr.id IS NOT NULL THEN home_addr.id ELSE ofc_addr.id END AS addr_id
    FROM user
    LEFT JOIN user_address AS home_addr
      ON (user.id = home_addr.user_id AND home_addr.type = 'home')
    LEFT JOIN user_address AS ofc_addr
      ON (user.active_office_address_id = ofc_addr.id)
    WHERE user.id = ?)

This assumes that only one home address exists per user.