Jump to content

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


igorek24
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

Link to comment
Share on other sites

  • 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 )
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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.