Forum Moderators: open
table a: products id,title,price..etc
table b: products_pictures idPicture,idProd,picture,rank
the pictures (which are named all *_1.jpg, *_2.jpg etc)sometimes are inserted sometimes in an "abnormal" order (i understood there are some gaps in indexes or something like that);
i want to extract all the products and the coresspondinf picture *_1
my query:
select distinct p.id,p.title,pp.picture from products p
LEFT JOIN products_pictures pp ON pp.idProd=p.id
group by p.id
order by pp.ordine asc
in my case it does not count the order by clause and returns *_3.jpg
the picture is not the right one and is streched;
the only fix i came out with is to reorder the table on primary key
what am i missing here? any help will be greatly appreciated
[edited by: encyclo at 11:11 am (utc) on Dec. 7, 2007]
[edit reason] no URLs thanks [/edit]
Also, it might be an issue with using an ORDER BY on fields that aren't selected- I've never tried that (ordering by a non-selected field in an aggregate query).
[edited by: LifeinAsia at 6:33 pm (utc) on Dec. 7, 2007]
I would suggest that you simplify the query and then eliminate what you don't need in the WHERE clause.
SELECT p.id,p.title,pp.picture
FROM products p JOIN products_pictures pp
ON pp.idProd=p.id
ORDER BY pp.ordine asc
PS: i have to add that the query here is very simplified compared with waht i have as original where i also have some avg functions which need a group by also
..maybe someone had the same issue....
