Posted by M@ on 06/09/05 16:30

Hi,

I have a problem with a left join. When I run it, the ordering isn't right.
Here's the SQL:

SELECT
manufacturers.man_name,
models.mdl_name,
cars.car_reg_number,
car_images.carimg_file,
MIN(car_images.carimg_order_num) AS img_order_num
FROM
manufacturers,
models,
cars
LEFT JOIN car_images
ON cars.car_id = car_images.carimg_car_id
WHERE
manufacturers.man_id = models.mdl_man_id
AND models.mdl_id = cars.car_mdl_id
GROUP BY
car_images.carimg_car_id
ORDER BY
car_images.carimg_order_num,
cars.car_price DESC


What should happen is that the images are sorted by the order number column
in car_images and the lowest value is chosen. However, what actually
happens is that the images are grouped together by the carimg_car_id field
in the car_images table but MySQL selects the most recently added image to
the database (the one with the highest ID number.)

What's wrong with my SQL statement?


Thanks in advance.

• Next in forum: Re: HELP ??
• Prev in forum: non funziona-not function
• Thread view: Left join messes up order by

[Reply to this message]