MySQL离开Join不像我预期的那样工作

Question

表名:功能

+--------+----------+
| fea_id | fea_name |
+--------+----------+
|      1 | Price    |
|      2 | Height   |
|      3 | Weight   |
+--------+----------+

表名: property_meta

+----+--------+--------+-------+
| id   | fea_id | pro_id | value |
+----+--------+--------+-------+
|  100 |      1 |    300 |  2500 |
|  200 |      2 |    300 |   300 |
|  
+----+--------+--------+-------+

我的疑问

SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id where property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC

预期结果

+--------+--------+-------+
| fea_id | pro_id | value |
+--------+--------+-------+
|      1 |    300 | 2500  |
|      2 |    300 | 300   |
|      3 |    300 | NULL  |
+--------+--------+-------+

但是我得到的是没有最后一行的。，我需要最后一行，也需要。如何修改我的查询以获得最后一行？

这意味着我需要获得特性表的所有行，即使属性元表中没有值。

Answer 1

where property_meta.pro_id=300把你的left join变成一个inner join。如果将其添加到on子句中，则它正在工作：

SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id and property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC

Answer 2

SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id and property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC

将where条件放入联接条件，因为where条件限制结果，而联接条件仅限制联接表

Answer 3

SELECT * FROM feature LEFT JOIN property_meta 

ON feature.fea_id = property_meta.fea_id 
   AND property_meta.pro_id=300 -- <-- need to move this condition here or the where clause will remove the last row

GROUP by feature.fea_id ORDER BY feature.fea_id ASC