SQL for NC_PARAPHRASE_VERBAL_LEFT_4a


SELECT SUM(freq) FROM (
SELECT COUNT(*) AS freq
FROM biotext_annotation sent
JOIN biotext_annotation word3 ON
   sent.pmid = word3.pmid AND
   sent.section = word3.section AND
   sent.sentence = word3.sentence AND
   sent.first_word_pos <= word3.first_word_pos
JOIN biotext_annotation verb ON
   sent.pmid = verb.pmid AND
   sent.section = verb.section AND
   sent.sentence = verb.sentence AND
   word3.last_word_pos = verb.first_word_pos
JOIN biotext_annotation word1 ON
   sent.pmid = word1.pmid AND
   sent.section = word1.section AND
   sent.sentence = word1.sentence AND
   verb.last_word_pos = word1.first_word_pos
JOIN biotext_annotation word2 ON
   sent.pmid = word2.pmid AND
   sent.section = word2.section AND
   sent.sentence = word2.sentence AND
   word1.last_word_pos = word2.first_word_pos AND
   word2.last_word_pos <= sent.last_word_pos
WHERE sent.layer_id IN (4) 
  AND word3.layer_id IN (1) 
  AND word3.tag_type IN (27, 28, 29, 30) 
  AND word3.word_id IN (43556, 45714, 1325075, 242403, 43610, 45717) 
  AND verb.layer_id IN (1) 
  AND verb.tag_type IN (53, 54, 55, 56, 57, 58) 
  AND word1.layer_id IN (1) 
  AND word1.tag_type IN (27, 28, 29, 30) 
  AND word1.word_id IN (70483, 73473, 1541807) 
  AND word2.layer_id IN (1) 
  AND word2.tag_type IN (27, 28, 29, 30) 
  AND word2.word_id IN (217291, 48437, 49764, 1248998, 123636, 1304352)

UNION ALL

SELECT COUNT(*) AS freq
FROM biotext_annotation sent
JOIN biotext_annotation word3 ON
   sent.pmid = word3.pmid AND
   sent.section = word3.section AND
   sent.sentence = word3.sentence AND
   sent.first_word_pos <= word3.first_word_pos
JOIN biotext_annotation verb ON
   sent.pmid = verb.pmid AND
   sent.section = verb.section AND
   sent.sentence = verb.sentence AND
   word3.last_word_pos = verb.first_word_pos
JOIN biotext_annotation det ON
   sent.pmid = det.pmid AND
   sent.section = det.section AND
   sent.sentence = det.sentence AND
   verb.last_word_pos = det.first_word_pos
JOIN biotext_annotation word1 ON
   sent.pmid = word1.pmid AND
   sent.section = word1.section AND
   sent.sentence = word1.sentence AND
   det.last_word_pos = word1.first_word_pos
JOIN biotext_annotation word2 ON
   sent.pmid = word2.pmid AND
   sent.section = word2.section AND
   sent.sentence = word2.sentence AND
   word1.last_word_pos = word2.first_word_pos AND
   word2.last_word_pos <= sent.last_word_pos
WHERE sent.layer_id IN (4) 
  AND word3.layer_id IN (1) 
  AND word3.tag_type IN (27, 28, 29, 30) 
  AND word3.word_id IN (43556, 45714, 1325075, 242403, 43610, 45717) 
  AND verb.layer_id IN (1) 
  AND verb.tag_type IN (53, 54, 55, 56, 57, 58) 
  AND det.layer_id IN (1) 
  AND det.tag_type IN (15) 
  AND det.word_id IN (213391, 1595216, 1625502, 100564, 103713, 103924, 12848, 17852, 317290, 15157, 19411, 19749) 
  AND word1.layer_id IN (1) 
  AND word1.tag_type IN (27, 28, 29, 30) 
  AND word1.word_id IN (70483, 73473, 1541807) 
  AND word2.layer_id IN (1) 
  AND word2.tag_type IN (27, 28, 29, 30) 
  AND word2.word_id IN (217291, 48437, 49764, 1248998, 123636, 1304352)

UNION ALL

SELECT COUNT(*) AS freq
FROM biotext_annotation sent
JOIN biotext_annotation word3 ON
   sent.pmid = word3.pmid AND
   sent.section = word3.section AND
   sent.sentence = word3.sentence AND
   sent.first_word_pos <= word3.first_word_pos
JOIN biotext_annotation verb ON
   sent.pmid = verb.pmid AND
   sent.section = verb.section AND
   sent.sentence = verb.sentence AND
   word3.last_word_pos = verb.first_word_pos
JOIN biotext_annotation prep ON
   sent.pmid = prep.pmid AND
   sent.section = prep.section AND
   sent.sentence = prep.sentence AND
   verb.last_word_pos = prep.first_word_pos
JOIN biotext_annotation word1 ON
   sent.pmid = word1.pmid AND
   sent.section = word1.section AND
   sent.sentence = word1.sentence AND
   prep.last_word_pos = word1.first_word_pos
JOIN biotext_annotation word2 ON
   sent.pmid = word2.pmid AND
   sent.section = word2.section AND
   sent.sentence = word2.sentence AND
   word1.last_word_pos = word2.first_word_pos AND
   word2.last_word_pos <= sent.last_word_pos
WHERE sent.layer_id IN (4) 
  AND word3.layer_id IN (1) 
  AND word3.tag_type IN (27, 28, 29, 30) 
  AND word3.word_id IN (43556, 45714, 1325075, 242403, 43610, 45717) 
  AND verb.layer_id IN (1) 
  AND verb.tag_type IN (53, 54, 55, 56, 57, 58) 
  AND prep.layer_id IN (1) 
  AND prep.tag_type IN (19) 
  AND word1.layer_id IN (1) 
  AND word1.tag_type IN (27, 28, 29, 30) 
  AND word1.word_id IN (70483, 73473, 1541807) 
  AND word2.layer_id IN (1) 
  AND word2.tag_type IN (27, 28, 29, 30) 
  AND word2.word_id IN (217291, 48437, 49764, 1248998, 123636, 1304352)

UNION ALL

SELECT COUNT(*) AS freq
FROM biotext_annotation sent
JOIN biotext_annotation word3 ON
   sent.pmid = word3.pmid AND
   sent.section = word3.section AND
   sent.sentence = word3.sentence AND
   sent.first_word_pos <= word3.first_word_pos
JOIN biotext_annotation verb ON
   sent.pmid = verb.pmid AND
   sent.section = verb.section AND
   sent.sentence = verb.sentence AND
   word3.last_word_pos = verb.first_word_pos
JOIN biotext_annotation prep ON
   sent.pmid = prep.pmid AND
   sent.section = prep.section AND
   sent.sentence = prep.sentence AND
   verb.last_word_pos = prep.first_word_pos
JOIN biotext_annotation det ON
   sent.pmid = det.pmid AND
   sent.section = det.section AND
   sent.sentence = det.sentence AND
   prep.last_word_pos = det.first_word_pos
JOIN biotext_annotation word1 ON
   sent.pmid = word1.pmid AND
   sent.section = word1.section AND
   sent.sentence = word1.sentence AND
   det.last_word_pos = word1.first_word_pos
JOIN biotext_annotation word2 ON
   sent.pmid = word2.pmid AND
   sent.section = word2.section AND
   sent.sentence = word2.sentence AND
   word1.last_word_pos = word2.first_word_pos AND
   word2.last_word_pos <= sent.last_word_pos
WHERE sent.layer_id IN (4) 
  AND word3.layer_id IN (1) 
  AND word3.tag_type IN (27, 28, 29, 30) 
  AND word3.word_id IN (43556, 45714, 1325075, 242403, 43610, 45717) 
  AND verb.layer_id IN (1) 
  AND verb.tag_type IN (53, 54, 55, 56, 57, 58) 
  AND prep.layer_id IN (1) 
  AND prep.tag_type IN (19) 
  AND det.layer_id IN (1) 
  AND det.tag_type IN (15) 
  AND det.word_id IN (213391, 1595216, 1625502, 100564, 103713, 103924, 12848, 17852, 317290, 15157, 19411, 19749) 
  AND word1.layer_id IN (1) 
  AND word1.tag_type IN (27, 28, 29, 30) 
  AND word1.word_id IN (70483, 73473, 1541807) 
  AND word2.layer_id IN (1) 
  AND word2.tag_type IN (27, 28, 29, 30) 
  AND word2.word_id IN (217291, 48437, 49764, 1248998, 123636, 1304352)
) AS cnt
;