All X for which all Y are Z MySQL query

-- ---------------------------------------------------------------------------
-- All X for which all Y are Z
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=832
-- ---------------------------------------------------------------------------

-- 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 x values in table X, a table of y
-- values in table Y, and a table of 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 very different solution see relational division.