How to do LEFT OUTER JOIN but only return first row if matched?

August 8, 2022

The schema is pretty straightforward:

A Business has many Customers
A Customer may have zero to many Phones
A Customer may have zero to many Emails
Both Phone and Email tables have an ‘IsPrimary’ bool field to reflect the primary one to use

So I tried this but that WHERE clause will effectively eliminate the LEFT OUTER JOIN’s ability to return records where there are zero Phones or Emails. If I leave the WHERE out I get multiple Phone/Emails when I just want the IsPrimary one (if exists). How do I tackle this best? Thanks

    SELECT b.BusinessName, c.FirstName, c.LastName, p.PhoneNumber, e.EmailAddress,
    FROM Business b 
    INNER JOIN Business b ON c.BusinessId = b.BusinessId 
    LEFT OUTER JOIN Phone p ON p.CustomerId = c.CustomerId
    LEFT OUTER JOIN Email e ON e.CustomerId = c.CustomerId
    WHERE p.IsPrimary = true AND e.IsPrimary = true

PS: this is MySQL 5.6

>Solution :

The condition should be moved to ON clause:

SELECT b.BusinessName, c.FirstName, c.LastName, p.PhoneNumber, e.EmailAddress,
FROM Business b 
INNER JOIN Business b ON c.BusinessId = b.BusinessId 
LEFT OUTER JOIN Phone p ON p.CustomerId = c.CustomerId AND p.IsPrimary = true
LEFT OUTER JOIN Email e ON e.CustomerId = c.CustomerId AND e.IsPrimary = true