LEFT JOIN based on content of a field in matched rows

Question

Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields.

Specifically, I have a users table and a contact table. The contact table ties a user to a piece of contact information (such as phone, email, or a myriad others). But I only want to pull in rows from the contact table that match a specific contact type (i.e. only pull phone records), but I still want to pull all of the records from the users table regardless of whether they have a phone record or not. This is in MariaDB.

Users table (simplified):

mysql> select * from users;                                                                                                          
+----+-----------+----------+---------------------------------+
| id | firstname | lastname | title                           |
+----+-----------+----------+---------------------------------+
|  1 | Chris     | Topher   | Ordinary Citizen Extraordinaire |
|  2 | Howard    | Roark    | Solutions Architect             |
|  3 | John      | Snow     | Knows Nothing                   |
+----+-----------+----------+---------------------------------+

Contact table:

mysql> select * from contact;
+----+--------+-------------+-------------------+
| id | userid | contacttype | contactdetails    |
+----+--------+-------------+-------------------+
|  1 |      1 | email       | [email protected]     |
|  2 |      1 | phone       | 1-800-OHNOES1     |
|  3 |      2 | email       | hroark@amazon.com |
+----+--------+-------------+-------------------+

What I'm trying to select is something like this:

    +-----------+----------+---------------------------------+-------------------+
    | firstname | lastname | title                           | contactdetails    |
    +-----------+----------+---------------------------------+-------------------+
    | Chris     | Topher   | Ordinary Citizen Extraordinaire | [email protected]     |
    | Howard    | Roark    | Solutions Architect             | [email protected] |
    | John      | Snow     | Knows Nothing                   | NULL              |
    +-----------+----------+---------------------------------+-------------------+

But my attempts with a left join result in too many or too few records:

mysql> select u.firstname,u.lastname,u.title,c.contactdetails from users u left join contact c on u.id=c.userid;
+-----------+----------+---------------------------------+-------------------+
| firstname | lastname | title                           | contactdetails    |
+-----------+----------+---------------------------------+-------------------+
| Chris     | Topher   | Ordinary Citizen Extraordinaire | [email protected]     |
| Chris     | Topher   | Ordinary Citizen Extraordinaire | 1-800-OHNOES1     |
| Howard    | Roark    | Solutions Architect             | [email protected] |
| John      | Snow     | Knows Nothing                   | NULL              |
+-----------+----------+---------------------------------+-------------------+
4 rows in set (0.00 sec)


mysql> select u.firstname,u.lastname,u.title,c.contactdetails from users u left    
join contact c on u.id=c.userid WHERE c.contacttype='email';
+-----------+----------+---------------------------------+-------------------+
| firstname | lastname | title                           | contactdetails    |
+-----------+----------+---------------------------------+-------------------+
| Chris     | Topher   | Ordinary Citizen Extraordinaire | [email protected]     |
| Howard    | Roark    | Solutions Architect             | [email protected] |
+-----------+----------+---------------------------------+-------------------+

Is this possible in a single query? I'm mostly only familiar with the simple syntax of SQL, but I've tried EXCEPT clauses and subqueries to no avail. Redesigning the schema isn't out of the question, but it does have to account for the fact that users will have multiple forms of contact information (most have 4-5 emails/phones) to include contact types that we don't know about yet.

Thoughts?

Answer 1

By moving c.contacttype='email' from WHERE to ON, the LEFT JOIN properties that if it doesn't match a criteria a NULL row appears behaviour occurs:

select u.firstname,u.lastname,u.title,c.contactdetails
from users u
left join contact c
  on u.id=c.userid and c.contacttype='email'