All X for which all Y are Z

from the Artful Common Queries page


You have an election database with tables for candidates, parties and districts. A candidate belongs to one party; a district may have any number of candidates:
DROP TABLE IF EXISTS 
  parties,districts,candidates;
CREATE TABLE parties (
  party char(12) NOT NULL,
  PRIMARY KEY (party)
);
INSERT INTO parties VALUES 
  ('Conservative'),('Liberal'),
  ('Socialist'),('Green'),('Libertarian');

CREATE TABLE districts (
  district char(10) DEFAULT NULL
);
INSERT INTO districts VALUES ('Essex'),
  ('Malton'),('Riverdale'),('Guelph'),('Halton');

CREATE TABLE candidates (
  id int(11) NOT NULL,
  name char(10) DEFAULT NULL,
  district char(10) DEFAULT NULL,
  party char(10) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO candidates VALUES 
(1,'Anne Jones','Essex','Liberal'),
(2,'Mary Smith','Malton','Liberal'),
(3,'Sara Black','Riverdale','Liberal'),
(4,'Paul Jones','Essex','Socialist'),
(5,'Ed White','Essex','Conservative'),
(6,'Jim Kelly','Malton','Liberal'),
(7,'Fred Price','Riverdale','Socialist'),
(8,'Bill Green','Guelph','Green'),
(9,'Garth Adams','Halton','Libertarian'),
(10,'Sam Adams','Guelph','Liberal'),
(11,'Jill Mackay','Halton','Liberal');
What query shows which parties have candidates in all districts? The simplest solution is to aggregate on party from a join of candidates to districts, and condition the result on each party having a rowcount at least equal to the district count:
SELECT party
FROM candidates
INNER JOIN districts USING (district)
GROUP BY party
HAVING COUNT(party) >= 
  (SELECT COUNT(*) FROM districts);
+---------+
| party   |
+---------+
| Liberal |
+---------+
The query pattern is: given a table of unique x values in table X, a table of unique y values in table Y, and a table of unique x-y pairs in table Z, use the following query to find all X.x which are paired with every Y.y in Z:
SELECT x
FROM Y JOIN Z USING (y)
GROUP BY x
HAVING COUNT(x) >= 
  (SELECT COUNT(*) FROM y);
For a different approach see "All X for which all Y are Z (relational division)".

Last updated 27 Jan 2020




Return to the Artful Common Queries page