Using a join-table twice in one query

Say tables a and b each have their own id column (primary key) and sundry
other columns. Table j has columns aid and bid to join a and b many-to-many.

Now, I want to select the joined a/b rows where the b rows are joined to (a
different set of) rows in a that meet condition x (which refers only to a).
Saying it in two sentences: Find the set Bx of rows in b joined to rows in a
that meet x. Now select a table of all joined a/b rows where the b rows are
in set Bx.

Using an IN-subquery to mimic the two-sentence formulation was very slow, as
the manual warns it can be:

SELECT ...
FROM b
INNER JOIN j ON j.bid=b.id
INNER JOIN a ON j.aid=a.id
WHERE b.id IN
( SELECT jx.bid
FROM a ax
INNER JOIN j jx ON jx.aid=ax.id
WHERE x )

Joining b to a via j twice, once on the way out and again on the way home,
was very fast but only with STRAIGHT_JOIN:

SELECT STRAIGHT_JOIN ...
FROM a ax
JOIN j jx ON jx.aid=ax.id
JOIN b ON jx.bid=b.id
JOIN j ON j.bid=b.id
JOIN a ON j.aid=a.id
WHERE x

What other approaches should I consider?

(Is standard stuff in database text books?)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org