Re: join with 1 row limit

I posted basically the same question last year and did not get a good
answer.

I expect that in the original query below; there are multiple images per
product in product_images and for this query the OP wants only one of
those images per product in the final result.  OP still wants all
products in the final query.

I will use this query to rephrase my question of last year.

How would we modify this JOIN clause to return the smallest (or largest)
image for each product?  In case of tie (multiple images with same size)
we are once again back to the OP's question - how to return only one image?

Frank




On 01/15/11 06:45, Vibhor Kumar wrote:
> Try:
>
> SELECT * FROM products p LEFT JOIN products_images pi ON p.id = pi.product_id WHERE products.company = 7 LIMIT 1;
>
> On Jan 15, 2011, at 8:02 AM, Behringer Behringer wrote:
>
>> SELECT *
>> FROM
>> products p LEFT JOIN
>> products_images pi ON p.id = pi.product_id  LIMIT 1
>> WHERE products.company = 7
>
> Thanks&  Regards,
> Vibhor Kumar
>
>
>
>
>
>
>
>
>
>
>