Hi all!
I'm using subqueries in MySQL 4.1.1 alpha. I use three subqueries and then want to get
those combinations matching a given constraints. It is a very simple task, but I get
less results than I expected. In fact, I tried to execute the same query in ORACLE and
I get 23 results (in MySQL I only get 9).
I wanted to know whether subqueries are not fully developed in this alpha version or
it is because MySQL behaves differently. Here is the query:
SELECT i1.id_document , i1.id_item , i2.id_item
FROM
(SELECT DISTINCT j.* FROM item j, itemtriplet it
WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word <> 0
AND it.id_triplet IN
(SELECT t.id_triplet FROM triplet t, unit u
WHERE u.alphacode_unit = 'N' AND u.name_level = 'MORPHO-SYNTACTIC'
AND t.id_unit = u.id_unit)
AND it.id_item = j.id_item
AND it.is_revised = 0 ) AS i1,
(SELECT DISTINCT j.* FROM item j, itemtriplet it
WHERE j.id_document = 4 AND j.id_annotator = 1 AND j.word <> 0
AND it.id_triplet IN
(SELECT t.id_triplet FROM triplet t, unit u
WHERE u.alphacode_unit = 'N' AND u.name_level = 'MORPHO-SYNTACTIC'
AND t.id_unit = u.id_unit)
AND it.id_item = j.id_item
AND it.is_revised = 0 ) AS i2
WHERE i1.paragraph = i2.paragraph
AND i1.sentence = i2.sentence
AND 3 = i2.token - i1.token
Thanks a lot!