Unlinked genes N-Z

This query is marked as a draft This query has been published by Certes.

List FOO (gene) articles which are not linked directly or indirectly to the corresponding FOO article.

SQL

SET STATEMENT max_statement_time = 120 FOR
SELECT Pg.page_title
FROM page Pg, page Pb
WHERE Pg.page_namespace=0 AND Pg.page_title LIKE 'A%_(gene)'
AND Pb.page_namespace=0 AND Pb.page_title=CONCAT(SUBSTR(Pg.page_title, 1, CHAR_LENGTH(Pg.page_title)-7),"")
AND NOT EXISTS ( -- redirect from AAK1 (gene) to AAK1
  SELECT 1
  FROM redirect
  WHERE rd_from=Pg.page_id AND rd_namespace=0 AND rd_title=Pb.page_title)
AND NOT EXISTS ( -- wikilink from ADNP to ADNP (gene)
  SELECT 1
  FROM pagelinks
  WHERE pl_from=Pb.page_id AND pl_from_namespace=0 AND pl_namespace=0 AND pl_title=Pg.page_title)
AND NOT EXISTS ( -- wikilink from BMX (disambiguation) → BMX (gene)
  SELECT 1
  FROM page Pd1, pagelinks
  WHERE Pd1.page_namespace=0 AND Pd1.page_title=CONCAT(SUBSTR(Pg.page_title, 1, CHAR_LENGTH(Pg.page_title)-7),"_(disambiguation)")
  AND pl_from=Pd1.page_id AND pl_from_namespace=0 AND pl_namespace=0 AND pl_title=Pg.page_title)
/*AND NOT EXISTS ( -- No examples found: wikilink+redirect chain FOO → R ⇒ FOO (gene)
  SELECT 1
  FROM pagelinks, page Pr1, redirect
  WHERE pl_from=Pb.page_id AND pl_from_namespace=0 AND pl_namespace=0
  AND Pr1.page_namespace=0 AND Pr1.page_title=pl_title AND Pr1.page_is_redirect=1
  AND rd_from=Pr1.page_id AND rd_namespace=0 AND rd_title=Pg.page_title)*/
AND NOT EXISTS ( -- Agouti (disambiguation) links to Agouti gene redirects to Agouti (gene)
  SELECT 1
  FROM page Pd2, pagelinks, page Pr2, redirect
  WHERE Pd2.page_namespace=0 AND Pd2.page_title=CONCAT(SUBSTR(Pg.page_title, 1, CHAR_LENGTH(Pg.page_title)-7),"_(disambiguation)")
  AND pl_from=Pd2.page_id AND pl_from_namespace=0 AND pl_namespace=0
  AND Pr2.page_namespace=0 AND Pr2.page_title=pl_title AND Pr2.page_is_redirect=1
  AND rd_from=Pr2.page_id AND rd_namespace=0 AND rd_title=Pg.page_title)
AND NOT EXISTS ( -- AAMP redirects to African American Museum in Philadelphia links to AAMP (gene)
  SELECT 1
  FROM redirect, page Pa1, pagelinks
  WHERE Pb.page_is_redirect=1 AND rd_from=Pb.page_id AND rd_namespace=0
  AND Pa1.page_namespace=0 AND Pa1.page_title=rd_title
  AND pl_from_namespace=0 AND pl_from=Pa1.page_id AND pl_namespace=0 AND pl_title=Pg.page_title)
/*AND NOT EXISTS ( -- No wikilink from FOO to the target of redirect FOO (gene)
  SELECT 1
  FROM redirect, pagelinks
  WHERE Pg.page_is_redirect=1 AND rd_from=Pg.page_id AND rd_namespace=0
  AND pl_from=Pb.page_id AND pl_from_namespace=0 AND pl_namespace=0 AND pl_title=rd_title)*/
AND NOT EXISTS ( -- No wikilink from FOO (disambiguation) to the target of redirect FOO (gene)
  SELECT 1
  FROM page Pdr, redirect, pagelinks
  WHERE Pdr.page_namespace=0 AND Pdr.page_title=CONCAT(SUBSTR(Pg.page_title, 1, CHAR_LENGTH(Pg.page_title)-7),"_(disambiguation)")
  AND Pdr.page_is_redirect=1 AND rd_from=Pdr.page_id AND rd_namespace=0
  AND pl_from=Pdr.page_id AND pl_from_namespace=0 AND pl_namespace=0 AND pl_title=rd_title)
AND NOT EXISTS ( -- No wikilink from FOO to another redirect to the target of redirect FOO (gene)
  SELECT 1
  FROM redirect Rb1, pagelinks, page Px1, redirect Rx1
  WHERE Pg.page_is_redirect=1 AND Rb1.rd_from=Pg.page_id AND Rb1.rd_namespace=0
  AND pl_from=Pb.page_id AND pl_from_namespace=0 AND pl_namespace=0
  AND Px1.page_namespace=0 AND Px1.page_title=pl_title
  AND Rx1.rd_from=Px1.page_id AND Rx1.rd_namespace=0 AND Rx1.rd_title=Rb1.rd_title)
-- TO DO -- No wikilink from FOO (disambiguation) to another redirect to the target of redirect FOO (gene)
AND NOT EXISTS ( -- [[A-1]] no wikilink from the target of redirect FOO to the target of redirect FOO (gene)
  SELECT 1
  FROM redirect Rb2, page Pbr, redirect Rg2, pagelinks
  WHERE Pb.page_is_redirect=1 AND Rb2.rd_from=Pb.page_id AND Rb2.rd_namespace=0
  AND Pbr.page_namespace=0 AND Pbr.page_title=Rb2.rd_title
  AND Pg.page_is_redirect=1 AND Rg2.rd_from=Pg.page_id AND Rg2.rd_namespace=0
  AND pl_from=Pbr.page_id AND pl_from_namespace=0 AND pl_namespace=0 AND pl_title=Rg2.rd_title)

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...