Subject | Avoiding multiple self-joins - any other way? |
---|---|

Author | heyredcoat |

Post date | 2005-09-27T21:37:56Z |

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

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