Untitled query #11252

SQL

USE wikidatawiki_p;
SELECT page_title, pi_type, COUNT( tl_title ) AS c, GROUP_CONCAT( REPLACE( tl_title, "Constraint:", "" ) SEPARATOR ", " )
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(tl_title = "Constraint:Type", 1, 0) "type",
    IF(tl_title = "Constraint:Value_only", 1, 0) "value",
    IF(tl_title = "Constraint:Qualifier", 1, 0) "qualifier",
    IF(tl_title = "Constraint:Source", 1, 0) "source",
    IF(tl_title = "Constraint:Value_type", 1, 0) "value type",
    IF(tl_title = "Constraint:Format", 1, 0) "format",
    IF(tl_title = "Constraint:Item", 1, 0) "item",
    IF(tl_title = "Constraint:Target_required_claim", 1, 0) "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...
