left join issue

left join order by problem

[cretum]

let's consider the following situation;

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]

[LifeinAsia]

Without knowing what "ordine" is in the products_pictures table, it's impossible to answer.

[cretum]

i deeply apologize... ordine = order (in romanian)...

[LifeinAsia]

Then I would check the values in that field.

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]

[syber]

I don't think you should be using GROUP BY here. You are trying to combine summary information with detail. Also, you don't need the LEFT JOIN unless you have products without pictures.

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

[cretum]

syber thx for your response but going your way does not help as i only need to get one row per each product..without group by i'lll have as many rows as a product has pictures;

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