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       | one@two.Three     |
|  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 | one@two.Three     |
    | Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
    | 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 | one@two.Three     |
| Chris     | Topher   | Ordinary Citizen Extraordinaire | 1-800-OHNOES1     |
| Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
| 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 | one@two.Three     |
| Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
+-----------+----------+---------------------------------+-------------------+

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
    c.contacttype='email' is part of the ON join critiera. – danblack Jan 22 '19 at 2:59
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'
  • 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 – TopherIsSwell Jan 23 '19 at 0:10
  • Glad it made sense. Accepted answers and/or feedback are quite sufficient. Well asked question too. – danblack Jan 23 '19 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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