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)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO parties VALUES ('Conservative'),('Liberal'),('Socialist'),('Green'),('Libertarian');

CREATE TABLE districts (
  district char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
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)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
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)".

Return to the Artful Common Queries page