Subject Re: Avoiding multiple self-joins - any other way?
Author Svein Erling Tysvær
Hi David!

Like Kjell, I do not quite understand what you are trying to do. Show
us some real SQL (i.e. with JOIN and aliases), show us the PLAN and
tell us a little bit about the indexes and their selectivity. My guess
is that [NOT] EXISTS might help you, but your information so far is
really too sparse to tell.


--- In, "heyredcoat" wrote:
> Hi,
> 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 way!
> Does anyone know a trick here?
> cheers,
> David