Subject Avoiding multiple self-joins - any other way?
Author heyredcoat

I have 3 tables - A, B with A foreign key, C with B foreign key. So A
is associated with multiple rows in B, each of which is associated
with multiple rows in C.

I need to perform a query to find all the rows in A for which there
are multiple B-C pairs ie
where (B.VALUE = "X" and C.NAME = "Y1" AND C.VALUE="Z1") AND
(B.VALUE = "X" and C.NAME = "Y2" AND C.VALUE="Z2") AND
(B.VALUE = "X" and C.NAME = "Y3" AND C.VALUE="Z3") AND
(B.VALUE = "X" and C.NAME = "Y4" AND C.VALUE="Z4") AND
(B.VALUE = "W" and C.NAME = "Y5" AND C.VALUE="Z5") AND
(B.VALUE = "W" and C.NAME = "Y6" AND C.VALUE="Z6") AND
(B.VALUE = "W" and C.NAME = "Y7" AND C.VALUE="Z7") AND

The only way I can figure out how to do this is with multiple
self-joins. So in the above example, I join A with B twice, and C 7
times! It is extremely slow, and I am thinking there must be another

Does anyone know a trick here?