左连接不返回所有行

2021-11-20 00:00:00 left-join mysql

我在 MySQL 中有这个查询:

I have this query in MySQL:

SELECT pr.*, pr7.value AS `room_price_high`
FROM `jos_hp_properties` pr
LEFT OUTER JOIN `jos_hp_properties2` pr7 ON pr7.property=pr.id
WHERE pr7.field=23

jos_hp_properties 表有 27 行，但查询只返回一行.基于这个问题我认为可能是因为 WHERE 子句.jos_hp_properties2 表有字段 id、property、field、value，其中 field 是第三个表的外键(我不需要从中获取数据).

The jos_hp_properties table has 27 rows but the query only returns one. Based on this question I think it may be because of the WHERE clause. The jos_hp_properties2 table has fields id, property, field, value, where field is a foreign key to a third table (which I don't need to get data from).

有没有办法从第一个表中选择所有行，包括表 #2 中字段为 23 的值(如果没有字段 23，则为 NULL)?

Is there a way to select all the rows from the first table, including the value from table #2 where the field is 23 (or NULL if there is no field 23)?

推荐答案

好的.将 WHERE 条件移至 JOIN:

Sure. Move the WHERE condition to the JOIN:

SELECT pr.*, pr7.value AS `room_price_high`
  FROM `jos_hp_properties` pr
       LEFT JOIN `jos_hp_properties2` pr7 
       ON pr7.property=pr.id
   AND 
       pr7.field=23

相关文章