时间戳

首页 > 解决方案 > 如何进行有条件联接？

postgresql - 如何进行有条件联接？

问题描述

我有以下查询

SELECT p.first_name || ' ' || p.last_name as full_name, p.user_id,
            u.email, u.username,
            (
                SELECT EXTRACT (year from (age(now(), p.date_of_birth)))
            )age,
            co.name as country, s.name as state, c.name as city,
            (
                SELECT country.phonecode FROM countries country WHERE ph.country_id = country.id
            )phone_code, ph.area_code, ph.number as phone_number,
            (
                SELECT i.path FROM images i WHERE i.id = p.image_id
            )avatar
            FROM people p
            JOIN users u ON u.id = p.user_id
            JOIN addresses a ON a.id = p.address_id
            JOIN cities c ON c.id = a.city_id
            JOIN states s ON s.id = c.state_id
            JOIN countries co ON co.id = s.country_id 
            JOIN phones ph ON ph.person_id = p.id
            WHERE (
                SELECT u.status FROM users u WHERE u.id = p.user_id
            ) = 'U'
            ORDER BY p.id DESC

但是，有些人不一定有信息，Address而Phone我无论如何都想带上这些信息，我该怎么做？

小例子：

用户

+----+-----------+-----------+---------------+-------------------------+
| id | username  | role      |     status    |          email          |
+----+-----------+-----------+---------------+-------------------------+
| 7  | Shibari   | P         | U             | deathwrap@live.ca       |
| 12 | Josh23    | P         | U             | heeman@gmail.ca         |
| 13 | Test22    | P         | U             | test@gmail.ca           |
+----+-----------+-----------+---------------+-------------------------+

人们

+----+----------+-----------+-------------+-------+------------+----------+
| id |first_name| last_name |date_of_birth|user_id| address_id | image_id |
+----+----------+-----------+-------------+-------+------------+----------+
| 7  | Edward   | Test      | 1983-10-13  | 7     |  11        | [null]   |
| 12 | Josh     | Test      |  1996-04-11 | 12    |  [null]    | 7        |
| 13 | Gavin    | Cringe    | 1991-04-11  | 13    |  18        | 8        |
+----+----------+-----------+-------------+-------+-----------------------+

地址

+----+---------------------+-----------+---------------+
| id | name                | zip_code  |     city_id   |
+----+---------------------+-----------+---------------+
| 11 | Rue De La Fonderie  |[null]     | 26497         |
| 18 | Av. Test            |[null]     | 6486          |
+----+---------------------+-----------+---------------+

城市

+-----+---------------------+-----------+
| id  | name                | state_id  |
+-----+---------------------+-----------+
|6486 | Resistencia         |210        |
|26497| Luxembourg          |2237       |
+-----+---------------------+-----------+

状态

+-----+---------------------+-----------+
| id  | name                |country_id |
+-----+---------------------+-----------+
|210  | Chaco               |10         |
|2237 | Luxembourg          |127        |
+-----+---------------------+-----------+

国家

+-----+---------------------+-----------+
| id  | name                | phonecode |
+-----+---------------------+-----------+
|10   | Argentina           |54         |
|127  | Luxembourg          |352        |
+-----+---------------------+-----------+

电话

+----+-----------+-----------+---------------+-------------+
| id | area_code | number    |  person_id    |  country_id |
+----+-----------+-----------+---------------+-------------+
| 6  | 661       | 12345     | 7             | 127         |
+----+-----------+-----------+---------------+-------------+

图片

+-----+----------------+
| id  | path           |
+-----+----------------+
|7    | 7.jpg          |
|8    | 8.jpg          |
+-----+----------------+

这是我想要的输出

+------------+-------+-----------------+--------+---+----------+----------+-----------+----------+
|  full_name |user_id|     email       |username|age|country   |state     |city       |phone_code|
+------------+-------+-----------------+--------+---+----------+----------+-----------+----------+
|Edward Test |7      |deathwrap@live.ca|Shibari |37 |Luxembourg|Luxembourg|Luxembourg |352       |
|Josh Test   |12     |heeman@gmail.ca  |Josh23  |24 |[null]    |[null]    |[null]     |[null]    |
|Gavin Cringe|13     |test@gmail.ca    |Test22  |29 |Argentina |Chaco     |Resistencia|[null]    |
+------------+-------+-----------------+--------+---+----------+----------+-----------+----------+

+------------+------------+-----------------+
|  area_code |phone_number|     avatar      |
+------------+------------+-----------------+
|661         |12345       | [null]          |       
|[null]      |[null]      | 7.jpg           |
|[null]      |[null]      | 8.jpg           |    
+------------+------------+-----------------+

目前它只是打印第一个条目，因为它同时链接了地址和电话号码，但是即使没有链接电话号码或地址，我也想打印此信息，我该怎么做？

标签： postgresql

解决方案

使用左连接而不是内连接：

SELECT ...
FROM people p
LEFT JOIN users u ON u.id = p.user_id AND u.status = 'U'
LEFT JOIN addresses a ON a.id = p.address_id
LEFT JOIN cities c ON c.id = a.city_id
LEFT JOIN states s ON s.id = c.state_id
LEFT JOIN countries co ON co.id = s.country_id 
LEFT JOIN phones ph ON ph.person_id = p.id

使用上面的左连接方法应该确保每个人员记录都出现在输出中，即使它与一个或多个 otber 表不匹配。请注意，我已将检查status从WHERE子句移至ON与表的左连接子句users。

推荐阅读