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屋！