JOIN后选择DISTINCT值 [英] SELECT DISTINCT values after a JOIN
问题描述
我有3张表:
车辆:vehicle_id,vehicle_type
1,摩托车
2,汽车
3,面包车
车主:person_id,vehicle_id,date_bought
1,1,2009
1,2,2008
2,3,2009
2,1,2005
我想显示所有车辆名称的列表。如果 person_id = 1
, date_bought
也应该返回。
SELECT * FROM车辆
LEFT JOIN车主使用(vehicle_id )
它返回这个:
<$ p $摩托车,2009年b $ b 1,2,车,2008
2,3,面包车,2009
2,1,摩托车,2005
然而,我现在无法将其缩小到所需的结果。如果我使用 DISTINCT(car_id)
,没有任何变化,因为我已经在JOIN之前选择了不同的car id;他们在加入之后只是不明显的。如果我使用 WHERE person_id = 1
,我删除最后2行,并且对面包车的所有引用都消失了。如果我使用 GROUP BY car_id
,则第一行和最后一行合并,但摩托车的 date_bought
是任意选择的。我想要的是:
1,1摩托车2009
1,2汽车2008
,3,van,
我需要一个不同的车号,但是这种情况发生在JOIN所以根本没有效果。如何获得JOIN的唯一性?
您需要在连接中包含person id限制,并使用外连接。即使您要加入的表中没有记录,也要使用外连接来返回信息。试试
SELECT person_id,vehicles。*
FROM车辆
LEFT OUTER JOIN车辆上的车辆.vehicle_id = owners.vehicle_id
和person_id = 1
I have 3 tables:
Vehicle: vehicle_id, vehicle_type
1, motorcycle
2, car
3, van
Owners: person_id, vehicle_id, date_bought
1, 1, 2009
1, 2, 2008
2, 3, 2009
2, 1, 2005
I want to display a list of all vehicle names. If the person_id = 1
, date_bought
should also be returned.
So I thought I would start with this:
SELECT * FROM vehicles
LEFT JOIN Owners USING (vehicle_id)
which returns this:
1, 1, motorcycle, 2009
1, 2, car, 2008
2, 3, van, 2009
2, 1, motorcycle, 2005
However, I now cannot narrow this down to the needed result. If I use DISTINCT(car_id)
, there is no change as I am already choosing distinct car ids before the JOIN; they are only non-distinct after the join. If I use WHERE person_id = 1
, I remove the last 2 rows and all reference to the van is gone. If I use GROUP BY car_id
, the first and last rows are combined but the date_bought
for the motorcycle is chosen arbitrarily. What I want is this:
1, 1, motorcycle, 2009
1, 2, car, 2008
, 3, van,
I need to require a distinct car id but this happens before the JOIN and so has no effect at all. How can I get the uniqueness with the JOIN?
You need to include the restriction on person id in your join and use an outer join. Outer joins are used when you want to return information even if there are no records in the table you're joining to. Try
SELECT person_id, vehicles.*
FROM vehicles
LEFT OUTER JOIN Owners on vehicles.vehicle_id = owners.vehicle_id
and person_id = 1
这篇关于JOIN后选择DISTINCT值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!