I have this query
SELECT model,year FROM new_vehicles WHERE make=Ford
UNION
SELECT model,year FROM old_vehicles WHERE make=Ford ORDER BY model
I have a while statement in PHP after this to get a list of those models along with year.
The problem is that this prints out duplicate models in the list. If I don't use the year in the query, I get unique list of models. I need the year as I use it in WHILE.
I added distinct after SELECTs above but it didn't change result, likely because UNION returns uniques anyway.
Anyway how to get unique models and include year?
Use the 'group by' clause to return one row per model but you then need to decide which year you want to see. I assume the latest.
It may be worthwhile including the 'make' in the query as well. It may make reporting easier.
Untested
SELECT make, model, max(year)
FROM new_vehicles
WHERE make = 'Ford'
GROUP_BY make, model
ORDER BY make, model
UNION
SELECT model, max(year)
FROM old_vehicles
WHERE make = 'Ford'
GROUP_BY make, model
ORDER BY make, model
Try this...
SELECT distinct(nv.model),
nv.year as newModelYear,
ov.year as oldModelYear
FROM new_vehicles nv, old_vehicles ov
WHERE
nv.make = ov.make and
nv.make= 'Ford'