Help with limiting mysql to unique JOIN row print on php page

[igorek24]

Hi all,

can someone help me to limit output from SELECT query to unique row only on php page. When I join a veh_services table to vehicles, and there are multiple rows in veh_services with the same veh_id, it outputs multiple rows with the same veh_id.

 

My query:

SELECT username, vehicles.veh_id, year, veh_make.make_title, veh_model.model_title, veh_registration.policy_num, cylinder_count, eng_hp, trans, int_color, ext_color, fuel_tank, date_format(veh_services.date,'%m/%d/%Y') as servdate
FROM vehicles
LEFT JOIN (veh_make, veh_model) ON (vehicles.model_id = veh_model.model_id) AND (vehicles.make_id = veh_make.make_id)
LEFT JOIN (veh_registration) ON (vehicles.reg_id = veh_registration.reg_id)
JOIN (users) on (vehicles.user_id = users.user_id)
left JOIN (veh_services) ON (veh_services.veh_id = vehicles.veh_id)
WHERE users.user_id = 2 

I've created a temp user in my phpMyAdmin:

User: help

Pass: Password01

 

Here is my DB typology

 

and here is my source code

and the webpage

[QuickOldCar]

See if this does the trick for you

SELECT DISTINCT username, vehicles.veh_id, year, veh_make.make_title, veh_model.model_title, veh_registration.policy_num, cylinder_count, eng_hp, trans, int_color, ext_color, fuel_tank, date_format(veh_services.date,'%m/%d/%Y') as servdate
FROM vehicles
LEFT JOIN (veh_make, veh_model) ON (vehicles.model_id = veh_model.model_id) AND (vehicles.make_id = veh_make.make_id)
LEFT JOIN (veh_registration) ON (vehicles.reg_id = veh_registration.reg_id)
JOIN (users) on (vehicles.user_id = users.user_id)
left JOIN (veh_services) ON (veh_services.veh_id = vehicles.veh_id)
WHERE users.user_id = 2 
GROUP BY (veh_id )

[Barand]

That is the nature of joins. Data from the vehicle is joined to each matching service record's data. As you want to show the service date, how would you expect a single record if there are multiple services?

[igorek24]

 

See if this does the trick for you

SELECT DISTINCT username, vehicles.veh_id, year, veh_make.make_title, veh_model.model_title, veh_registration.policy_num, cylinder_count, eng_hp, trans, int_color, ext_color, fuel_tank, date_format(veh_services.date,'%m/%d/%Y') as servdate
FROM vehicles
LEFT JOIN (veh_make, veh_model) ON (vehicles.model_id = veh_model.model_id) AND (vehicles.make_id = veh_make.make_id)
LEFT JOIN (veh_registration) ON (vehicles.reg_id = veh_registration.reg_id)
JOIN (users) on (vehicles.user_id = users.user_id)
left JOIN (veh_services) ON (veh_services.veh_id = vehicles.veh_id)
WHERE users.user_id = 2 
GROUP BY (veh_id )

That seems to work just like I want it. Thank you so much