Hi Peter and the other readers,
In short, my question is:
Is is possible to get the same results as what I get with the 4 steps I detailed in my first post with a more efficient query?
Now, in detailed and with a practical example so that you can understand why I want these results:
+-----+-----+
| eid | aid |
+-----+-----+
| X | a |
| X | b |
| Y | a |
| Z | b |
| T | a |
| U | a |
+-----+-----+
Let's say that the eids are in fact words, and the aids are written documents.
I am interesting in finding other words mentioned together in a document with the words 'X' and 'Y' (case A). After I can't find anymore connection between 'X' and 'Y' and other words, I'm looking at connections between 'X' and another word (case B), and 'Y' and another word (case C).
Case A:
'X' is mentioned in the article 'a', and so is 'Y'. 'T' and 'U' are also mentioned in the article 'a'. This correspond to the case A. 'X', 'Y', and 'T' are mentioned only in the article 'a' so the count is 1. (same for 'X', 'Y', 'U').
+------+------+------+-----------+
| eid1 | eid2 | eid3 | count(aid)|
+------+------+------+-----------+
| X | Y | T | 1 |
| X | Y | U | 1 |
+------+------+------+-----------+
Case B:
'X' is mentioned in the article 'b', but not 'Y'. 'Z' is also mentioned in the article 'b' so this corresponds to the answer of the case B. There is no other article mentioning 'X' so there is no more results than this for case B:
+------+------+------+-----------+
| eid1 | eid2 | eid3 | count(aid)|
+------+------+------+-----------+
| X | | Z | 1 |
+------+------+------+-----------+
(note: we
don't want lines like this one:
+------+------+------+-----------+
| eid1 | eid2 | eid3 | count(aid)|
+------+------+------+-----------+
| X | | T | 1 |
+------+------+------+-----------+
because this information is already covered by case A).
Case C:
There is no article where 'Y' is mentioned. So there is no rows corresponding to the case C.
As detailed in my first post, I know how to get these results. I create a table, then I insert the rows corresponding to case A, then the ones corresponding to case B, and then the one to case C. I make a select on the table to get my results. Finally I delete the table.
I hope you can help me to reduce the sql steps necessary to achieve this!
Amélie.
PS: Thank you Peter for the trick to format the code!
Edited 1 time(s). Last edit at 11/19/2008 02:27AM by amelie a.