The tables are the
following:
CREATE TABLE `bsg_cert` (
<span> `id` int(</span>11) NOT NULL
AUTO_INCREMENT,
<span> `title` varchar(</span>255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
CREATE TABLE `bsg_cert_people`
(
<span> `cid` int(</span>11) NOT NULL DEFAULT '0',
<span> `pid` int(</span>11) NOT NULL DEFAULT '0',
PRIMARY KEY (`cid`,`pid`),
KEY `pid` (`pid`),
CONSTRAINT `bsg_cert_people_ibfk_1`
FOREIGN KEY (`cid`) REFERENCES `bsg_cert` (`id`),
CONSTRAINT `bsg_cert_people_ibfk_2`
FOREIGN KEY (`pid`) REFERENCES `bsg_people`
(`id`)
) ENGINE=InnoDB
CREATE TABLE `bsg_people`
(
<span> `id` int(</span>11) NOT NULL
AUTO_INCREMENT,
<span> `fname` varchar(</span>255) NOT NULL,
<span> `lname` varchar(</span>255) DEFAULT NULL,
<span> `homeworld` int(</span>11) DEFAULT NULL,
<span> `age` int(</span>11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `homeworld`
(`homeworld`),
CONSTRAINT `bsg_people_ibfk_1`
FOREIGN KEY (`homeworld`)
REFERENCES `bsg_planets`
(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB
CREATE TABLE `bsg_planets`
(
<span> `id` int(</span>11) NOT NULL
AUTO_INCREMENT,
<span> `name` varchar(</span>255) NOT NULL,
<span> `population` bigint(</span>20) DEFAULT NULL,
<span> `language` varchar(</span>255) DEFAULT NULL,
<span> `capital` varchar(</span>255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB
Joining them all up doing a
count with a group by should do the trick:
SELECT planet.name ,
COUNT(*) AS
cert_count
FROM
bsg_cert_people people_cert
JOIN bsg_people
people ON people.id = people_cert.pid
JOIN bsg_planet
planet ON people.homeworld = planet.id
GROUP BY planet.name
Or we can also use this syntax to get the same result:
SELECT pl.name, count(cert) AS "CertCount"
FROM bsg_planets pl
JOIN bsg_people pe ON pl.id = pe.homeworld
JOIN bsg_cert_people cp ON cp.pid = pe.id
GROUP BY pl.id