mysql SQL：3个具有NULL值的表连接

啊...我一直在用头撞这个，因为我似乎想不明白。
我的表：

$ select * from my_family_members;
+-----------+-----------+-----------------------------+
| member_id | house_id  | member_name                 |
+-----------+-----------+-----------------------------+
|  2        |         2 | Ash                         |
|  5        |         5 | Mel                         |
|  8        |         2 | NULL                        |
+-----------+-----------+-----------------------------+

$ select * from my_houses;
+------------------------------------------+
| house_id | code     | house_name         |
+------------------------------------------+
|  2       | house1   | My Blue house      |
|  5       | house2   | My Red house       |
|  8       | house3   | My Green house     |
|  9       | house4   | My Pink House      |
+------------------------------------------+

$ select * from my_pets;
+-----------+--------------+----------------+
|house_id   | config_key   | config_value   |
+-----------+--------------+----------------+
| 2         | cat          | Tom            |
| 2         | cat          | Jerry          |
| 5         | dog          | Buster         |
| 8         | cat          | Bubbles        |        
+-----------+--------------+----------------+

我尝试做一个查询，将得到我的house_id，member_id和config_value的猫，包括NULL值。即：

+---------------+------------------------------+
| house_name    | member_name  |  cats         |
+---------------+------------------------------+
| My Blue House | Ash          |  Jerry        |
| My Blue House | Ash          |  Tom          |
| My Red House  | Mel          |  NULL         |
| My Green House| NULL         |  Bubbles      |
| My Pink House | NULL         |  NULL         |
+---------------+------------------------------+

我尝试了一个类似的查询：

select mh.house_name, mfm.member_name, config_value as cats
from my_pets mp
LEFT OUTER JOIN my_houses mh
ON mp.house_id=mh.house_id 
JOIN my_family_members mfm
ON mfm.house_id=mh.house_id
where config_key = 'cat' order by house_name;

但我只能得到：

+---------------+------------------------------+
| house_name    | member_name  |  cats         |
+---------------+------------------------------+
| My Blue House | Ash          |  Jerry        |
| My Blue House | Ash          |  Tom          |
+---------------+------------------------------+

我需要改变什么才能得到我想要的结果？
编辑：逻辑是总有一所房子，但房子可以有一只猫，而实际上没有人住在里面，所以member_name是NULL，cat不是NULL（可怜的猫。.），也可以有一个成员没有猫的房子（cat是NULL，但member不是NULL），最后也可以有一个房子没有人，也没有猫（cat是NULL，member_name是NULL，虽然可能有狗，但我只想看看房子里是否有猫或人）。
进一步编辑：我在这里有一个严重的错误，当我把我的实际问题翻译成一个假设的问题时。我意识到在my_pets表中，它实际上有house_id列，而不是member_id列。抱歉造成了混乱。.....我已经更正了上面的数据集。
谢谢！

你似乎从错误的方向来处理这个问题。你想要所有的房子，所以从那个表开始（my_houses）。然后，你希望每个房子都有人居住-不排除没有人的房子-所以左加入人（我的家庭成员）到房子。然后你想要任何存在的宠物-不排除房子/人-所以左加入宠物（my_pets）到人。
比如说

SELECT ...
FROM my_houses h
LEFT OUTER JOIN my_family_members fm ON h.house_id = fm.house_id
LEFT OUTER JOIN my_pets p ON fm.member_id = p.member_id

第二个答案

我假设成员8的名字为空是一个错字，因为一个人必须有一个名字？希望这能给你你想要的。如果没有，那么您可能需要考虑结束这个问题，花一些时间来思考您想要实现的目标，然后创建一个新问题，准确描述表结构、所有可能的场景和您想要的结果

WITH members AS (
    SELECT H.HOUSE_ID, M.MEMBER_ID, M.MEMBER_NAME
    FROM my_houses H
    LEFT OUTER JOIN my_family_members M ON H.HOUSE_ID = M.HOUSE_ID
),
PETS AS (
    SELECT H.HOUSE_ID, P.CONFIG_VALUE
    FROM my_houses H
    LEFT OUTER JOIN my_pets P ON H.HOUSE_ID = P.HOUSE_ID AND P.CONFIG_KEY = 'cat'
),
members_pets AS(
    SELECT M.HOUSE_ID, M.MEMBER_ID, M.MEMBER_NAME, P.CONFIG_VALUE
    FROM members M
    FULL OUTER JOIN pets P ON M.HOUSE_ID = P.HOUSE_ID
)
SELECT H.HOUSE_ID, H.HOUSE_NAME
,MP.MEMBER_NAME, MP.CONFIG_VALUE
FROM my_houses H
LEFT OUTER JOIN members_pets MP ON H.HOUSE_ID = MP.HOUSE_ID
;

mysql SQL：3个具有NULL值的表连接

1条答案

相关问题

热门标签

最新问答