The subquery version of that logic
SELECT * FROM a
JOIN b ON a.id = b.a_id
WHERE b.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;
if the sorting column (id) is also the joining column (id).
Michael Caplan wrote:
> 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:
> a.id = b.a_id
> b.id =
> B as C
> C.a_id = a.id
> I'm wondering if you have any thoughts on this approach.
> Peter Brawley wrote:
>> >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;
>> 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:
>>> 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?
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