sql - Left join brainfart

Question

I seem to be thinking wrong today. I have two tables as follows

+--------+  +------------------------+
|person  |  |contact                 |
+--------+  +-----------------+------+
|id |name|  |person_id |type  | value|
+--------+  +------------------------+
|00 |a   |  |00        |phone |123   |
|01 |b   |  |00        |email |a@a.a |
|02 |c   |  |01        |phone |456   |
|03 |d   |  |02        |email |c@c.c |
+--------+  +------------------------+

and I need all persons and optionally their phone number, e.g.

+-----------+
|name |phone|
+-----------+
|a    |123  |
|b    |456  |
|c    |     |
|d    |     |
+-----+-----+

标签： sql

Answer 1

您确实需要左连接，但您需要在连接条件中包含对电话类型的限制：

select p.*, c.value as phone
from person p 
  left join contact c 
         on c.person_id = p.id 
        and c.type = 'phone'

如果您将限制c.type = 'phone'放入where子句中，则会将外部联接重新转换为内部联接。