SQL for NC3


SELECT LOWER(lql.compound) compound, 
       COUNT(*) AS freq
FROM (
   SELECT compound.content AS compound
FROM BIOTEXT_ANNOTATION compound
JOIN BIOTEXT_ANNOTATION table_1 ON
   compound.pmid = table_1.pmid AND
   compound.section = table_1.section AND
   compound.sentence = table_1.sentence AND
   compound.first_word_pos = table_1.first_word_pos
JOIN BIOTEXT_ANNOTATION table_2 ON
   compound.pmid = table_2.pmid AND
   compound.section = table_2.section AND
   compound.sentence = table_2.sentence AND
   table_1.last_word_pos = table_2.first_word_pos
JOIN BIOTEXT_ANNOTATION table_3 ON
   compound.pmid = table_3.pmid AND
   compound.section = table_3.section AND
   compound.sentence = table_3.sentence AND
   table_2.last_word_pos = table_3.first_word_pos AND
   compound.last_word_pos = table_3.last_word_pos
WHERE (compound.layer_id = 3 AND compound.tag_type IN (31, 1031))
   AND (table_1.layer_id = 1 AND table_1.tag_type IN (27, 28, 29, 30, 1027, 1028, 1029, 1030))
   AND (table_2.layer_id = 1 AND table_2.tag_type IN (27, 28, 29, 30, 1027, 1028, 1029, 1030))
   AND (table_3.layer_id = 1 AND table_3.tag_type IN (27, 28, 29, 30, 1027, 1028, 1029, 1030))
) AS lql
GROUP BY LOWER(lql.compound)
ORDER BY freq DESC