MySQL Merge 2 Tables, Join with a Third

[Frank_Rizzo]

I have two working procedures.

The first merges the results of 2 tables:

select * from car_sales where salesperson = 'Joe' UNION select * from truck_sales where salesperson = 'Joe'

2008-08-01,Joe,Car
2008-08-02,Joe,Truck
2008-08-03,Joe,Car

The next one joins a single sales table to a vehicles table:

select car_sales.*,vehicle_type,vehicle_manufacturer from car_sales LEFT JOIN vehicles on car_sales.vehicle_type = vehicles.vehicle_type where car_sales.salesperson = 'Joe'

That works too as it finds all records for Joe in car_sales and lookups the type of vehicle and manufacturer from the vehicles table.

e.g.

2008-08-01,Joe,Car,Toyota
2008-08-03,Joe,Car,Ford

What I need to do is combine both of the above. I need to generate a report for (Joe's car_sales and truck_sales) but also join with the vehicles table to find out the manufacturer

2008-08-01,Joe,Car,Toyota
2008-08-02,Joe,Truck,Dodge
2008-08-03,Joe,Car,Ford

Something like this does not work:

select car_sales.* from car_sales where car_sales.salesperson = 'Joe' UNION truck_sales where truck_sales.salesperson = 'Joe' LEFT JOIN vehicles on (car_sales.vehicle_type = vehicles.vehicle_type OR truck_sales.vehicle_type = vehicles.vehicle_type)

[Frank_Rizzo]

Worked it out. This is much easier than I thought. All I have to do is union the second select:

select car_sales.*,vehicle_type,vehicle_manufacturer from car_sales LEFT JOIN vehicles on car_sales.vehicle_type = vehicles.vehicle_type where car_sales.salesperson = 'Joe'

UNION

select truck_sales.*,vehicle_type,vehicle_manufacturer from truck_sales LEFT JOIN vehicles on truck_sales.vehicle_type = vehicles.vehicle_type where truck_sales.salesperson = 'Joe'