SQL for NC_PARAPHRASE_COPULA_RIGHT_4a


SELECT SUM(freq) FROM (

SELECT COUNT(*) AS freq
FROM biotext_annotation sent
JOIN biotext_annotation word2 ON
   sent.pmid = word2.pmid AND
   sent.section = word2.section AND
   sent.sentence = word2.sentence AND
   sent.first_word_pos <= word2.first_word_pos
JOIN biotext_annotation word3 ON
   sent.pmid = word3.pmid AND
   sent.section = word3.section AND
   sent.sentence = word3.sentence AND
   word2.last_word_pos = word3.first_word_pos
JOIN biotext_annotation that ON
   sent.pmid = that.pmid AND
   sent.section = that.section AND
   sent.sentence = that.sentence AND
   word3.last_word_pos = that.first_word_pos
JOIN biotext_annotation are ON
   sent.pmid = are.pmid AND
   sent.section = are.section AND
   sent.sentence = are.sentence AND
   that.last_word_pos = are.first_word_pos
JOIN biotext_annotation det ON
   sent.pmid = det.pmid AND
   sent.section = det.section AND
   sent.sentence = det.sentence AND
   are.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 AND
   word1.last_word_pos <= sent.last_word_pos
WHERE sent.layer_id IN (4) 
  AND ((word2.layer_id IN (1) 
  AND word2.tag_type IN (27, 28, 29, 30)) 
  AND word2.word_id IN (217291, 48437, 49764, 1248998)) 
  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 (that.layer_id IN (1) 
  AND (that.word_id IN (385724, 100558, 103709, 1160611, 108652, 109208, 1407157))) 
  AND (are.layer_id IN (1) 
  AND (are.word_id IN (56965, 58784, 58855, 16802, 20031, 20036, 20175))) 
  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, 183393, 70488))))

UNION ALL

SELECT COUNT(*) AS freq
FROM biotext_annotation sent
JOIN biotext_annotation word2 ON
   sent.pmid = word2.pmid AND
   sent.section = word2.section AND
   sent.sentence = word2.sentence AND
   sent.first_word_pos <= word2.first_word_pos
JOIN biotext_annotation word3 ON
   sent.pmid = word3.pmid AND
   sent.section = word3.section AND
   sent.sentence = word3.sentence AND
   word2.last_word_pos = word3.first_word_pos
JOIN biotext_annotation that ON
   sent.pmid = that.pmid AND
   sent.section = that.section AND
   sent.sentence = that.sentence AND
   word3.last_word_pos = that.first_word_pos
JOIN biotext_annotation are ON
   sent.pmid = are.pmid AND
   sent.section = are.section AND
   sent.sentence = are.sentence AND
   that.last_word_pos = are.first_word_pos
JOIN biotext_annotation word1 ON
   sent.pmid = word1.pmid AND
   sent.section = word1.section AND
   sent.sentence = word1.sentence AND
   are.last_word_pos = word1.first_word_pos AND
   word1.last_word_pos <= sent.last_word_pos
WHERE sent.layer_id IN (4) 
  AND ((word2.layer_id IN (1) 
  AND word2.tag_type IN (27, 28, 29, 30)) 
  AND word2.word_id IN (217291, 48437, 49764, 1248998)) 
  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 (that.layer_id IN (1) 
  AND (that.word_id IN (385724, 100558, 103709, 1160611, 108652, 109208, 1407157))) 
  AND (are.layer_id IN (1) 
  AND (are.word_id IN (56965, 58784, 58855, 16802, 20031, 20036, 20175))) 
  AND ((word1.layer_id IN (1) 
  AND word1.tag_type IN (27, 28, 29, 30)) 
  AND (word1.word_id IN (70483, 73473, 1541807, 183393, 70488)))
) AS cnt
;