List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 5 2006 1:57pm
Subject:Re: Limiting a Join
View as plain text  
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

Thread
Limiting a JoinMichael Caplan5 Aug
  • Re: Limiting a JoinPeter Brawley5 Aug
    • Re: Limiting a JoinMichael Caplan5 Aug
      • Re: Limiting a JoinPeter Brawley5 Aug