Untitled query #11252

SQL

USE wikidatawiki_p;
SELECT page_title, pi_type, COUNT( tl_title ) AS c, GROUP_CONCAT( REPLACE( tl_title, "Constraint:", "" ) SEPARATOR ", " ) AS constraints
FROM wb_property_info
JOIN page ON page_namespace = 121 AND page_title = CONCAT("P", pi_property_id)
LEFT JOIN templatelinks ON tl_title LIKE "Constraint:%" AND tl_namespace = 10 AND tl_from = page_id
GROUP BY pi_property_id
ORDER BY c, pi_property_id;
USE wikidatawiki_p;
SELECT page_title, pi_type, COUNT( tl_title ) AS c,
	IF("Constraint:Type" IN GROUP_CONCAT( tl_title ), 1, "") "type",
    IF("Constraint:Value_only" IN GROUP_CONCAT( tl_title ), 1, "") "value",
    IF("Constraint:Qualifier" IN GROUP_CONCAT( tl_title ), 1, "") "qualifier",
    IF("Constraint:Source" IN GROUP_CONCAT( tl_title ), 1, "") "source",
    IF("Constraint:Value_type" IN GROUP_CONCAT( tl_title ), 1, "") "value type",
    IF("Constraint:Format" IN GROUP_CONCAT( tl_title ), 1, "") "format",
    IF("Constraint:Item" IN GROUP_CONCAT( tl_title ), 1, "") "item",
    IF("Constraint:Target_required_claim" IN GROUP_CONCAT( tl_title ), 1, "") "required claim"
FROM wb_property_info
JOIN page ON page_namespace = 121 AND page_title = CONCAT("P", pi_property_id)
LEFT JOIN templatelinks ON tl_title LIKE "Constraint:%" AND tl_namespace = 10 AND tl_from = page_id
GROUP BY pi_property_id
ORDER BY c, pi_property_id;
/*
SELECT * FROM wb_property_info WHERE pi_type IN ("string", "external-id") AND NOT EXISTS (
  SELECT * FROM templatelinks WHERE tl_from IN (
    SELECT page_id FROM page WHERE page_namespace = 121 AND page_title = CONCAT("P", pi_property_id)
  ) AND tl_namespace = 10 AND tl_title = "Constraint:Format"
);*/
SQL

Checking query status...
