List:General Discussion« Previous MessageNext Message »
From:Michael Caplan Date:August 5 2006 1:22pm
Subject:Re: Limiting a Join
View as plain text  
Peter,

Thanks for the reply.  I was not able to get your query working as 
illustrated.  I also realize that my example query was flawed, as I made 
no reference to an ordering column (as you point out).  What I was able 
to get working is the following:


SELECT
  *
FROM
  a
JOIN
  b
ON
  a.id = b.a_id
WHERE
     b.id =
(
    SELECT
       MAX(C.ID)
    FROM
       B as C
    WHERE
       C.a_id = a.id
)


I'm wondering if you have any thoughts on this approach.

Best,

Michael


Peter Brawley wrote:
> 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
>>
>
>

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