From: Peter Brawley
Date: August 5 2006 1:57pm
Subject: Re: Limiting a Join
List-Archive: http://lists.mysql.com/mysql/200674
Message-Id: <44D4A3DF.5010009@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Michael,

 >If I wanted all records from "a" and only the first record from "b",
 >how would I integrate a LIMIT statement in this, or some other
 >statement that would achieve the same end?  Appending LIMIT
 >to the end of the query will limit the entire result set, which is not
 >the desired effect.

Assuming "first" means the smallest joined value on orderingcol ...

select a.*,b.*
from a inner join b on a.id=b.id
left join c on b.id=c.id and b.orderingcol < c.orderingcol
where c.orderingcol is null;

PB

-----

Michael Caplan wrote:
> Hi there,
>
> I'm following up on a thread I started yesterday with a new thread, 
> cause I'm now looking at  a different problem: limiting the result of 
> a join.  For example:
>
> SELECT
>   *
> FROM
>   a
> JOIN
>   b
> ON
>   a.id = b.id
>
> If I wanted all records from "a" and only the first record from "b", 
> how would I integrate a LIMIT statement in this, or some other 
> statement that would achieve the same end?  Appending LIMIT to the end 
> of the query will limit the entire result set, which is not the 
> desired effect.
>
> Any ideas?
>
> Thanks,
>
> Michael
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006