1

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?

1
  • 1
    c.contacttype='email' is part of the ON join critiera.
    – danblack
    Jan 22, 2019 at 2:59

1 Answer 1

0

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'
2
  • Fantastic, that works perfectly. In retrospect, that makes a lot of sense. I don't have the reputation to upvote, but excellent answer, thank you Jan 23, 2019 at 0:10
  • Glad it made sense. Accepted answers and/or feedback are quite sufficient. Well asked question too.
    – danblack
    Jan 23, 2019 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.