igorek24 Posted June 10, 2015 Share Posted June 10, 2015 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 Quote Link to comment Share on other sites More sharing options...
Solution QuickOldCar Posted June 10, 2015 Solution Share Posted June 10, 2015 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 ) Quote Link to comment Share on other sites More sharing options...
Barand Posted June 10, 2015 Share Posted June 10, 2015 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? Quote Link to comment Share on other sites More sharing options...
igorek24 Posted June 10, 2015 Author Share Posted June 10, 2015 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.