MySQL: LEFT JOIN table with preference for specific rows-CodePudding

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

CodePudding user response：

You can use ROW_NUMBER() to sort phone numbers according to importance, and then pick the first one only.

For example:

select *
from (
  select
    c.*,
    n.*,
    row_number() over(partition by c.ContactID order by IsPrimary desc) as rn
  FROM Contact c
  LEFT JOIN ContactPhoneNumber cn ON cn.ContactID = c.ContactID
  LEFT JOIN PhoneNumber n ON cn.PhoneNumberID = n.PhoneNumberID
) x
where rn = 1

Result:

 ContactID  FirstName  PhoneNumberID  PhoneNumber   IsPrimary  rn 
 ---------- ---------- -------------- ------------- ---------- -- 
 1          Jim        2              555-727-7277  1          1  
 2          John       3              444-727-7277  0          1  
 3          Timothy    null           null          null       1

See running example at DB Fiddle.

CodePudding user response：

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