Mysql的独家左边的一个连接到许多关系
问题描述:
我有这两个表Mysql的独家左边的一个连接到许多关系
facilities
+----+--------------------------------------+--------------+-------+------+------------+------------+
| id | name | category | image | cust | created_at | updated_at |
+----+--------------------------------------+--------------+-------+------+------------+------------+
| 1 | Bar | hotel | | | NULL | NULL |
| 2 | Minibar in alle Zimmer | hotel | | | NULL | NULL |
| 3 | Snack restaurant | hotel | | | NULL | NULL |
| 4 | Grillroom | hotel | | | NULL | NULL |
+----+--------------------------------------+--------------+-------+------+------------+------------+
表 - 2
resortfacilities
+----+------+---------------------+---------------------+------+
| id | f_id | created_at | updated_at | h_id |
+----+------+---------------------+---------------------+------+
| 42 | 4 | 2016-09-21 13:17:27 | 2016-09-21 13:17:27 | 35 |
| 59 | 1 | 2016-09-22 10:23:27 | 2016-09-22 10:23:27 | 38 |
| 60 | 4 | NULL | NULL | 38 |
+----+------+---------------------+---------------------+------+
而且我跑这下面的SQL
SELECT
facilities.id,
facilities.`name`,
facilities.image,
resortfacilities.h_id
FROM
facilities
LEFT JOIN resortfacilities ON facilities.id = resortfacilities.f_id
WHERE
resortfacilities.f_id IS NULL AND
facilities.category = 'hotel' OR
resortfacilities.h_id <> 35
与期望得到以下结果
+----+---------------------------+-------+------+
| id | name | image | h_id |
+----+---------------------------+-------+------+
| 4 | Grillroom | | 38 |
| 2 | Minibar in alle Zimmer | | NULL |
| 3 | Snack restaurant | | NULL |
+----+---------------------------+-------+------+
与出属于h_id 35
的值,但我最终
+----+---------------------------+-------+------+
| id | name | image | h_id |
+----+---------------------------+-------+------+
| 1 | Bar | | 38 |
| 4 | Grillroom | | 38 |
| 2 | Minibar in alle Zimmer | | NULL |
| 3 | Snack restaurant | | NULL |
+----+---------------------------+-------+------+
我想知道如何得到这一结果?
答
使用下面的脚本(用于获得上述结果)。
SELECT
f.id,
f.`name`,
f.image,
r.h_id
FROM
facilities f
LEFT JOIN resortfacilities r ON f.id = f.f_id AND r.h_id <> 35
WHERE f.category = 'hotel'
+0
检查你的别名 – Serg
+0
@serg更新..感谢通知。 –
尝试把括号内为表达** resortfacilities.f_id IS NULL和 facilities.category = '酒店' OR resortfacilities.h_id 35 ** – StanislavL
它看起来对我来说,你的数据不是适当规范化。例如,为什么created_at出现在两个表中? – e4c5
最有可能'WHERE facilities.category = '酒店' AND( resortfacilities.f_id IS NULL OR resortfacilities.h_id 35)' – Serg