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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
