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;

SELECT page_title, pi_type, COUNT( tl_title ) AS c,
	IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Type", 1, "") "type",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Value_only", 1, "") "value",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Qualifier", 1, "") "qualifier",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Source", 1, "") "source",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Value_type", 1, "") "value type",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Format", 1, "") "format",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Item", 1, "") "item",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Target_required_claim", 1, "") "required claim",
    IF(GROUP_CONCAT( tl_title ) LIKE "Constraint:Qualifiers", 1, "") "qualifiers"
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...
