我正在使用Microsoft Access,我有这个SQL查询,它执行所有相关的连接:
SELECT c.ID
FROM ((((((((Cars c
INNER JOIN Offers
ON c.ID = Offers.car_id)
INNER JOIN Users u
ON c.owner_id = u.ID)
INNER JOIN City
ON u.city_id = City.ID)
INNER JOIN Models
ON c.model_id = Models.ID)
INNER JOIN Makes
ON Models.make_id = Makes.ID)
INNER JOIN Type
ON Models.type_id = Type.ID) ))
WHERE ( Offers.decision <> 3 )
在我的 Cars 表中,我有 1 辆车,在我的报价表中,我有 3 个针对同一辆车的报价,它们的 Offers.decision 为 2 和 4,出于某种原因,当我执行查询时,它显示同一辆车 3 次,就像它在 3 个报价的基础上一样。
他们是否是一种展示没有 Offers.decision of 3 的汽车的方式?
不应使用连接,而应使用子查询:
SELECT * FROM Cars WHERE ID NOT IN (SELECT DISTINCT car_id FROM Offers WHERE decision <> 3)
将查询简化为:
SELECT c.ID
FROM Cars c INNER JOIN
Offers ON c.ID = Offers.car_id
WHERE (Offers.decision <> 3)