SQL for NC_PARAPHRASE_COPULA_LEFT_4_sql


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 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
LEFT OUTER 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 AND 
   det.layer_id = 1 AND 
   det.tag_type = 15 AND 
   det.word_id IN (213391, 1595216, 1625502, 100564, 103713, 103924, 12848, 17852, 317290, 15157, 19411, 19749)
JOIN biotext_annotation word1 ON
   sent.pmid = word1.pmid AND
   sent.section = word1.section AND
   sent.sentence = word1.sentence AND
   COALESCE(det.last_word_pos,are.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 = 4
  AND word3.layer_id = 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 = 1
  AND that.word_id IN (385724, 100558, 103709, 1160611, 108652, 109208, 1407157, 108678, 109221, 109235)
  AND are.layer_id = 1
  AND are.word_id IN (56965, 58784, 58855, 16802, 20031, 20036, 20175)
  AND word1.layer_id = 1
  AND word1.tag_type IN (27, 28, 29, 30)
  AND word1.word_id IN (70483, 73473, 1541807)
  AND word2.layer_id = 1
  AND word2.tag_type IN (27, 28, 29, 30)
  AND word2.word_id IN (217291, 48437, 49764, 1248998, 123636, 1304352)
;