LEFT JOIN 基於匹配行中欄位的內容

通常，我正在嘗試獲取一個表並將其與另一個表連接，但僅在其中一個欄位中具有特定值的行上。

具體來說，我有一個使用者表和一個聯繫人表。聯繫人表將使用者與一條聯繫資訊（例如電話、電子郵件或無數其他資訊）聯繫起來。但我只想從聯繫人表中提取與特定聯繫人類型匹配的行（即只提取電話記錄），但我仍然想從使用者表中提取所有記錄，無論他們是否有電話記錄. 這是在 MariaDB 中。

使用者表（簡化）：

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

聯繫表：

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 |
+----+--------+-------------+-------------------+

我要選擇的是這樣的：

   +-----------+----------+---------------------------------+-------------------+
   | firstname | lastname | title                           | contactdetails    |
   +-----------+----------+---------------------------------+-------------------+
   | Chris     | Topher   | Ordinary Citizen Extraordinaire | one@two.Three     |
   | Howard    | Roark    | Solutions Architect             | hroark@amazon.com |
   | John      | Snow     | Knows Nothing                   | NULL              |
   +-----------+----------+---------------------------------+-------------------+

但是我嘗試使用左連接會導致記錄太多或太少：

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 |
+-----------+----------+---------------------------------+-------------------+

這可能在單個查詢中嗎？我大多只熟悉 SQL 的簡單語法，但我嘗試過 EXCEPT 子句和子查詢無濟於事。重新設計架構並非不可能，但它確實必須考慮到使用者將擁有多種形式的聯繫資訊（大多數有 4-5 封電子郵件/電話）以包含我們不知道的聯繫類型的事實然而。

想法？

通過c.contacttype='email'從WHEREto移動ON，LEFT JOIN如果它不符合條件，則會出現 NULL 行出現的行為：

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'

引用自：https://dba.stackexchange.com/questions/227726