Untitled query #11252

SQL

USE wikidatawiki_p;
SELECT page_title, pi_type, COUNT( tl_title ) AS c,
	IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Type%", "x", "") "type",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Value_only%", "x", "") "value",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Qualifier%", "x", "") "qualifier",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Source%", "x", "") "source",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Item%", "x", "") "item",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Qualifiers%", "x", "") "qualifiers",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Format%", "x", "") "format",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:One_of%", "x", "") "one of",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Value_type%", "x", "") "value type",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Target_required_claim%", "x", "") "target",
    IF(GROUP_CONCAT( tl_title ) LIKE "%Constraint:Units%", "x", "") "units"
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 page_title
ORDER BY c DESC, 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...
