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
STRAIGHT_JOIN page Pb ON Pb.page_namespace=0 AND Pb.page_title=SUBSTR(Pg.page_title, 1, CHAR_LENGTH(Pg.page_title)-7)
WHERE Pg.page_namespace=0 AND Pg.page_title >='N' AND Pg.page_title <'Zzzz' AND Pg.page_title LIKE '%_(gene)'
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(Pb.page_title,"_(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: FOO links to a redirect to 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, a redirect to Agouti (gene)
  SELECT 1
  FROM page Pd2, pagelinks, page Pr2, redirect
  WHERE Pd2.page_namespace=0 AND Pd2.page_title=CONCAT(Pb.page_title,"_(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, which 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 ( -- ABI3 links, and ABI3 (gene) redirects, to ABI gene family member 3
  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 examples found: FOO (disambiguation) links, and FOO (gene) redirects, to the same article
  SELECT 1
  FROM page Pd3, redirect, pagelinks
  WHERE Pd3.page_namespace=0 AND Pd3.page_title=CONCAT(Pb.page_title,"_(disambiguation)")
  AND Pg.page_is_redirect=1 AND rd_from=Pg.page_id AND rd_namespace=0
  AND pl_from=Pd3.page_id AND pl_from_namespace=0 AND pl_namespace=0 AND pl_title=rd_title)
AND NOT EXISTS ( -- BID links to BH3 interacting domain death agonist which, like BID (gene), redirects to BH3 interacting-domain death agonist
  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)
AND NOT EXISTS ( -- No examples found: FOO (disambiguation) links to Bar which, like FOO (gene), redirects to Baz
  SELECT 1
  FROM page Pd4, redirect Rd1, pagelinks, page Px1, redirect Rx1
  WHERE Pd4.page_namespace=0 AND Pd4.page_title=CONCAT(Pb.page_title,"_(disambiguation)")
  AND Pg.page_is_redirect=1 AND Rd1.rd_from=Pg.page_id AND Rd1.rd_namespace=0
  AND pl_from=Pd4.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=Rd1.rd_title)
AND NOT EXISTS ( -- A-1 redirects to A1 which links to List of A1 genes, proteins or receptors, which is the target of redirect A-1 (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...