SELECT DISTINCT values after a JOIN

Question

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?

Solution 1

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

Solution 2

This should return what you've listed as expected output:

   SELECT DISTINCT
          o.person_id,
          v.vehicle_id,
          v.vehicle_type,
          o.date_bought
     FROM VEHICLES v
LEFT JOIN OWNERS o ON o.vehicle_id = v.vehicle_id
LEFT JOIN (SELECT t.vehicle_id,
                  MAX(t.date_bought) 'max_date_bought'
            FROM OWNERS t
        GROUP BY t.vehicle_id) x ON x.vehicle_id = o.vehicle_id
                                AND x.max_date_bought = o.date_bought
    WHERE o.person_id IS NULL 
       OR o.person_id = 1

Be aware that because of the left join, the OWNERS columns will return NULL if there is no matching vehicle_id in the OWNERS table.