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)". |
|