MySQL: LEFT JOIN TWO tables with preference for specific rows

Offshoot of this question: MySQL: LEFT JOIN table with preference for specific rows

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

My issue is that I need to retrieve all contacts, whether they have a phone number / address or not. Additionally, for each contact, I need to retrieve their PRIMARY phone number and address if they have one. If not, retrieve a non primary phone number / address or lastly, return no phone number / address. Joining the tables is not an issue, but I’m having trouble coming up with a way to prefer the primary phone number / address over the non primary phone number / address . 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
LEFT JOIN ContactAddress ON ContactAddress.ContactID = Contact.ContactID
LEFT JOIN Address ON ContactAddress.AddressID = Address.AddressID
GROUP BY Contact.ContactID;

The SQL Fiddle of this problem can be found at http://sqlfiddle.com/#!9/3c24a5/4

Answer

Left join with the filtered by priority phones and addresses.

SELECT * 
FROM Contact c
LEFT JOIN (
  SELECT cn.ContactID, n.PhoneNumber,
    row_number() over(partition by cn.ContactID order by n.IsPrimary desc) as rn
  FROM ContactPhoneNumber cn 
  LEFT JOIN PhoneNumber n ON cn.PhoneNumberID = n.PhoneNumberID
) ph ON ph.ContactID = c.ContactID AND ph.rn = 1
LEFT JOIN (
  SELECT cn.ContactID, n.Address,
    row_number() over(partition by cn.ContactID order by n.IsPrimary desc) as rn
  FROM ContactAddress cn 
  LEFT JOIN Address n ON cn.ContactAddressID = n.ContactAddressID
) a ON a.ContactID = c.ContactID AND a.rn = 1