MySQL version differences

[specterious]

I created a select query that works in version 4.1 and does not in 4.0

It goes a little something like this...

select * from `table1`
left join (select `id` from `table2` order by `id` limit 1) on table1.id = table2.id

This works in 4.1, but 4.0 gives an error at left join ( so I can't use the nested select inside the left join. Does MySQL 4.0 not support that?

 

[Fark_Maniac]

http://dev.mysql.com/doc/refman/4.1/en/left-join-optimization.html

what about trying these syntaxes?

 

[surazal]

http://dev.mysql.com/doc/refman/4.1/en/mysql-4-1-nutshell.html

MySQL 4.1 added subqueries support which is what you are using when you use the select statement in the join part.

You can rewrite the query to not use subqueries.

 

[specterious]

Yea, I've tried to rewrite it but I can't think of anything that would replicate the limiting of 1 row.

select p.product_id, i.image_id
from `products` as p
left join `product_images_links` as i on (p.product_id = i.product_id)

There are multiple products and multiple images per product and I want to return only 1 image per product. How should I do it without using 2 queries?

 

[Fark_Maniac]

Yea, I've tried to rewrite it but I can't think of anything that would replicate the limiting of 1 row.

select p.product_id, i.image_id
from `products` as p
left join `product_images_links` as i on (p.product_id = i.product_id)

There are multiple products and multiple images per product and I want to return only 1 image per product. How should I do it without using 2 queries?

what happens if you use "where" instead of "on"?

select p.product_id, i.image_id
from `products` as p
left join `product_images_links` as i where p.product_id = i.product_id

 

[surazal]

You can use GROUP BY, for example

select p.product_id, i.image_id
from `products` as p
left join `product_images_links` as i on (p.product_id = i.product_id)
group by p.product_id

 

[specterious]

Oh, duh, group by product_id. I kept trying to group by image_id. Thanks, this gets me what I need.