MySQL If Else Statment

Question

Here is an example query:

SELECT tags.serial_number, tags.expiry_date, tags.activation_date, users.email, users.offers, users.newsletter, pets.name as petname
    FROM `tags`
    JOIN `users` on users.id = tags.user_id
    JOIN `pets` on pets.id = tags.pet_id
    WHERE  users.username = 'myusername' AND  users.email = 'itsme@mydomain.com'

In this scenario, the only results that will be returned are those tags that are assigned to both a user and a pet. But what I want is to be able to receive all tags, regardless of whether or not they are assigned to a pet.

If there some kind of "IF there is a pet, show the petname, ELSE just return the pet name as NULL" or something like that?

Answer 1

The quick answer is to use LEFT JOIN in place of JOIN.

(I'm sure this question has asked on StackOverflow before ...)

Answer 2

Try using a Left Outer Join Which will populate rows that don't join to the table with Nulls

SELECT tags.serial_number, tags.expiry_date, tags.activation_date, users.email, users.offers, users.newsletter, pets.name as petname
FROM `tags`
JOIN `users` on users.id = tags.user_id
Left Outer JOIN `pets` on pets.id = tags.pet_id
WHERE  users.username = 'myusername' AND  users.email = 'itsme@mydomain.com'