MySql Join

[ahashmi]

Hello Gentlemen,

The question i am going to ask would probably a very simple one to you but it consumed a lot of my time. So, i decided to put it here seeking your help.

So, here is the problem i am facing.

I've 3 tables as follows

1 - Maintenance_Performed
Log_id,
Type_code,
Date_Performed,
Vehicle_id

2 - Next_Maintenance
Log_id,
Type_code,
Planned_date,
Vehicle_id

3 - Maintenance_types
Type_code,
Type_name

I am trying to fetch recods for a vehicle that which type of maintenance was performed and what type is planned for the vehicles.

1st and 2nd table's Type_code is the foreign key to 3rd table.

i need to present the record as follows

vehicle_id | Performed_Maintenance | Planned_Maintenance | Planned_Date

Problem arises when i join the maintenance_types table to fetch related Maintenance name.

May you please help me out in this matter and write this query for me.

Thanks a lot.

[NogDog]

SELECT
	mp.vehicle_id,
	mt1.type_name AS performed,
	mt2.type_name AS planned,
	nm.planned_date
FROM maintenance_performed AS mp
INNER JOIN next_maintenance AS nm ON nm.vehicle_id = mp.vehicle_id
INNER JOIN maintenance_types AS mt1 ON mp.type_code = mt1.type_code
INNER JOIN maintenance_types AS mt2 ON nm.type_code = mt2.type_code

(This assumes there will be both a performed and planned entry for each vehicle of interest. If not, some of the joins might need to be left joins, instead, and perhaps some reordering of from/joins would be needed.)

[ahashmi]

Thank you very much NogDog. It worked.

Thanks again.

[cretaceous]

would it not be more logical to have one single table for "maintenance" ?
and in that table you store planned date and actual date maintenance performed - so you have only one record for each maintenance item

you can still join it to itself for compound queries