代码之家 › 专栏 › 技术社区 › Jimmy

SQL:有关左外部联接的帮助

query-optimization database-design database sql

Jimmy · 技术社区 · 16 年前

我的SQL似乎没有限制基于 price .

在我上一篇文章中, SQL: Help me optimize my SQL ,人们表示我应该使用左外联接。

SELECT homes.home_id, 
    address, 
    city, 
    state, 
    zip, 
    price, 
    photo_id, 
    photo_url_dir
FROM homes
LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id
AND primary_photo_group_id = home_photo_group_id
AND home_photo_type_id =2
AND display_status = true
AND homes.price BETWEEN 500000 AND 1000000

但是,它仍然显示价格为50万英镑的房子。

我不明白。当我有一个Where条件来限制这个字段时,为什么上面的SQL会显示价格低于500000的房子呢?

谢谢你的帮助。

我想做什么

我想根据以下条件显示带家庭和不带家庭照片的家庭 PRICE 在x和y之间…或 SQFT >z.但这些标准需要同时适用于带和那些 homes 没有 home_photo

这是正确的吗?

SELECT homes.home_id, 
    address, 
    city, 
    state, 
    zip, 
    price, 
    photo_id, 
    photo_url_dir
FROM homes
LEFT OUTER JOIN home_photos ON homes.home_id = home_photos.home_id
    AND homes.primary_photo_group_id = home_photos.home_photo_group_id
    AND home_photos.home_photo_type_id =2
WHERE homes.display_status = true
AND homes.price BETWEEN 500000 AND 1000000

4 回复 | 直到 16 年前

SQLMenace 16 年前

最后一行应该使用where not和

WHERE homes.price BETWEEN 500000 AND 1000000

最终结果是以下SQL:

SELECT 
    homes.home_id, 
    homes.address, 
    homes.city, 
    homes.state, 
    homes.zip, 
    homes.price, 
    home_photos.photo_id, 
    home_photos. photo_url_dir
FROM 
    homes
    LEFT OUTER JOIN home_photos ON 
        homes.home_id = home_photos.home_id
        AND homes.primary_photo_group_id = home_photos.home_photo_group_id
        AND home_photos.home_photo_type_id =2
WHERE
    homes.price BETWEEN 500000 AND 1000000
    AND homes.display_status = true

编辑

现在你的平方英尺会在

AND home_photos.home_photo_type_id =2 
AND SQFT <=2000 
WHERE homes.price BETWEEN 500000 AND 1000000

Rony 16 年前

在join关键字后使用join条件,在where子句后使用所有其他筛选条件

user110714 16 年前

试试这个…

SELECT homes.home_id,     
address,     
city,     
state,     
zip,     
price,     
photo_id,     
photo_url_dir
FROM 
homes
LEFT OUTER JOIN 
home_photos ON homes.home_id = home_photos.home_id
AND 
primary_photo_group_id = home_photo_group_id
WHERE 
home_photo_type_id =2
AND 
display_status = true
AND 
homes.price BETWEEN 500000 AND 1000000

Guffa 16 年前

你有所有的条件 join 你应该把它们放在 where 用于限制查询的子句。类似:

select
   homes.home_id, 
   address, 
   city, 
   state, 
   zip, 
   price, 
   photo_id, 
   photo_url_dir
from
   homes
left join
   home_photos on homes.home_id = home_photos.home_id
where
   primary_photo_group_id = home_photo_group_id and
   home_photo_type_id = 2 and
   display_status = true and
   homes.price BETWEEN 500000 AND 1000000

因为我不知道每个字段来自哪个表,所以我不知道上面的内容是否有意义。将条件划分为 参加 以及 哪里 合身。