heyredcoat wrote:

> 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?

Well, I don't really understand your where-clause snippet, but if I
understand the paragraph preceding it correctly, you want to select
those A records for which there are multiple records in B-join-C.

select A.primarykey, A.otherfield, A.onemorefield, ...
from A
inner join B on ...
inner join C on ...
group by A.primarykey, A.otherfield, A.onemorefield, ...
having count(*) > 1
order by A.primarykey --optional of course

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64