Jump to content

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


Go to solution Solved by QuickOldCar,

Recommended Posts

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

2015-06-09_20-54-33.png

 

and here is my source code

and the webpage

  • Solution

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 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?

 

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.