CODEWITHSUNDEEP
mysql
Andrew Brown
Andrew Brown

Reputation: 125

Mysql grab row if condition is met, grab another if not

I am trying to LEFT JOIN a user's address to the user's name. I want to join their mailing address first, but if they don't have a mailing address then I want to join their home address, which they WILL have. Im struggling to find the correct way to go about this. Its an easy if/ else.. If they have an address_type = 'Mailing' select this row, if not, select the row where address_type = 'Home'. Any help would be great.

Upvotes: 0

Views: 25

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

This sounds like doing two left joins along with COALESCE to pick the desired address:

SELECT
    u.name,
    COALESCE(a1.address, a2.address) AS address
FROM user u
LEFT JOIN address a1
    ON u.id = a1.user_id AND
       a1.address_type = 'Mailing'
LEFT JOIN address a2
    ON u.id = a2.user_id AND
       a2.address_type = 'Home';

The above logic will choose the mailing address first for each user, if it be available. Otherwise, it will default to using the home address.

Upvotes: 2

Related Questions