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

Michael

The subquery version of that logic

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
);

likely runs slower. If the sorting column is also the joining column, 
you can still write it as a join:

SELECT a.*, b.*
FROM a INNER JOIN b ON a.id=b.id
LEFT JOIN b AS c ON a.id=c.id and b.id < c.id
WHERE c.id IS NULL;

PB

-----

if the sorting column (id) is also the joining column (id).

PB


Michael Caplan wrote:
> 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
>>>
>>
>>
>
>


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