Re: Subqueries too slow

[Andreas Schlicker]

Jay,

I just tried your suggested query.

In order to get the right results, I also had to dublicate the join on the b table:

SELECT a.id, a.acc, a.name
FROM a
INNER JOIN b
ON a.id = b.a_id
INNER JOIN c
ON b.c_id=c.id
AND c.acc="X"
INNER JOIN b as b2
ON a.id = b2.a_id
INNER JOIN c as c2
ON b2.c_id=c2.id
WHERE c2.acc="Y";

However, this makes it easier to generate the SQL automatically. Most important, it's much faster than the original query.

Thank you very much.