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,
(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:


For a very different solution see relational division.

Return to the Artful Common Queries page