MySQL: LEFT JOIN table with preference for specific rows

Question

I have a Contacts table, a PhoneNumbers table, and a ContactPhoneNumber table. A Contact can haveMany PhoneNumbers via the ContactPhoneNumber pivot table. A PhoneNumber can be marked as primary or not. A contact can also have no primary phone number or in fact no phone number at all.

My issue is that I need to retrieve all contacts, whether they have a phone number or not. Additionally, for each contact, I need to retrieve their PRIMARY phone number if they have one. If not, retrieve a non primary phone number or lastly, return no phone number. Joining the tables is not an issue, but I'm having trouble coming up with a way to prefer the primary phone number over the non primary phone number. Because, if I add a WHERE isPrimary = 1 to the query, it's going to now eliminate those users who don't have a primary phone number. Instead, I simply need to prefer the 1, but also be OK with a 0 if no 1 exists. My query so far is as follows:

SELECT * FROM Contact
LEFT JOIN ContactPhoneNumber ON ContactPhoneNumber.ContactID = Contact.ContactID
LEFT JOIN PhoneNumber ON ContactPhoneNumber.PhoneNumberID = PhoneNumber.PhoneNumberID
GROUP BY Contact.ContactID;

The SQL Fiddle of this problem can be found at http://sqlfiddle.com/#!9/92e21e/1

Answer 1

SELECT t1.*, 
       t2.*, 
       COALESCE(t3.PhoneNumberID, t4.PhoneNumberID) PhoneNumberID, 
       COALESCE(t3.PhoneNumber, t4.PhoneNumber) PhoneNumber, 
       COALESCE(t3.IsPrimary, t4.IsPrimary) IsPrimary
FROM Contact t1
LEFT JOIN ContactPhoneNumber t2 ON t1.ContactID = t2.ContactID
LEFT JOIN PhoneNumber t3 ON t2.PhoneNumberID = t3.PhoneNumberID AND t3.IsPrimary
LEFT JOIN PhoneNumber t4 ON t2.PhoneNumberID = t4.PhoneNumberID
GROUP BY t1.ContactID;

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=92a31ad5270a5fe83433fb5c12613cdb