On 2006/10/11 Stuart wrote:
> [..]
Apologies for following up my own post, but after
struggling with that query for over a day, I figured
out the answer within thirty minutes of posting.
(Sigh)
My slow query was:
> SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
> FROM p
> JOIN a ON a.p=p.id
> LEFT JOIN (
> SELECT a.id AS aid, b.id as bid
> FROM a
> LEFT JOIN b ON b.p=a.p
> LEFT JOIN x ON x.a=a.id AND x.b=b.id
> WHERE x.a IS NULL
> ) AS sub ON sub.aid=a.id
> WHERE p.id=1;
Including the parent table p in the inner select was
all it took.
SELECT p.id AS pid, a.id AS aid, sub.bid AS bid FROM p JOIN a ON a.p=p.id LEFT JOIN ( SELECT
p.idAS pid, a.id AS aid, b.id as bid FROM p JOIN a ON a.p=p.id LEFT
JOINb ON b.p=a.p LEFT JOIN x ON x.a=a.id AND x.b=b.id WHERE x.a IS NULL ) AS sub ON
sub.aid=a.idAND sub.pid=p.id WHERE p.id=1;