[MySQL] merging 2 tables query problem

[boeykenk]

Hi,

I'm having a problem with a MySQL query between 2 tables.
Let me first show you the structure of my dbase and then explain the problem.

table 1: tblrelateds
id | predecessor | successor
1 | item1 | item2
2 | item1 | item3
3 | item4| item 5

table 2: tblitems
id | status | attribute
item1 | A | 1
item2 | B | 2
item3 | C | 3
item4 | B | 1
item5 | A | 3


I would like to have the following query:
search for all items in status "A" and attribute "1" which are related to items in status "B" and attribute "2".
"Related" means:

• one item is predecessor, the other is successor
• both are successors of a common predecessor

So the tblrelateds is a "one-to-many" relation. But a successor can/will not be a predecessor of another relation.

I have tried the following query:

select rel1.predecessor, tblitems.id, second.id from tblpr
join tblitems as second on second.status='B' and second.attribute='2'
join tblrelateds as rel1 on
(rel1.predecessor=tblitems.id or rel1.successor=tbitems.id)
join tblrelateds as rel2 on
(rel2.predecessor=second.id or rel2.successor=second.id)
where tblitems.status='A' and tblitems.attribute='1'
and rel1.predecessor=rel2.predecessor

I have tried to run this on a database with more than 100.000 records but after waiting for more than a minute I killed the query. I took too long. Is my query correct? And is there a faster way to do this?

[RobIII]

Probeer het gerust nog eens, in het Nederlands
Dit is een Nederlandstalig forum en de voertaal hier is dan ook gewoon Nederlands.

[ Voor 46% gewijzigd door RobIII op 07-04-2008 17:45 ]