By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE people (
id INT PRIMARY KEY,
name VARCHAR(20),
dob DATE,
requires_cert TINYINT
);
INSERT INTO people VALUES
(1, 'Person 1', '2000-01-01', 1),
(2, 'Person 2', '2006-06-06', 0),
(3, 'Person 3', '1980-01-01', 1);
CREATE TABLE cert_types (
id INT PRIMARY KEY,
name VARCHAR(20)
);
INSERT INTO cert_types VALUES
(1, 'Certification 1'),
(2, 'Certification 2');
CREATE TABLE certs (
person_id INT NOT NULL,
certification_id INT NOT NULL,
expiration DATE,
PRIMARY KEY (person_id, certification_id)
);
INSERT INTO certs VALUES
(1, 1, '2025-01-01'),
(3, 2, '2066-06-06'),
(1, 2, '2024-03-30');
Records: 3 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
SELECT p.*,
MAX(IF(c.certification_id = 1, c.expiration, NULL)) AS 'Certificate 1',
MAX(IF(c.certification_id = 2, c.expiration, NULL)) AS 'Certificate 2'
FROM people p
LEFT JOIN certs c ON p.id = c.person_id
GROUP BY p.id;
| id | name | dob | requires_cert | Certificate 1 | Certificate 2 |
|---|---|---|---|---|---|
| 1 | Person 1 | 2000-01-01 | 1 | 2025-01-01 | 2024-03-30 |
| 2 | Person 2 | 2006-06-06 | 0 | null | null |
| 3 | Person 3 | 1980-01-01 | 1 | null | 2066-06-06 |
SELECT p.*,
COALESCE(MAX(IF(c.certification_id = 1, c.expiration, NULL)), 'n/a') AS 'Certificate 1',
COALESCE(MAX(IF(c.certification_id = 2, c.expiration, NULL)), 'n/a') AS 'Certificate 2'
FROM people p
LEFT JOIN certs c ON p.id = c.person_id
GROUP BY p.id;
| id | name | dob | requires_cert | Certificate 1 | Certificate 2 |
|---|---|---|---|---|---|
| 1 | Person 1 | 2000-01-01 | 1 | 2025-01-01 | 2024-03-30 |
| 2 | Person 2 | 2006-06-06 | 0 | n/a | n/a |
| 3 | Person 3 | 1980-01-01 | 1 | n/a | 2066-06-06 |
SELECT p.*, GROUP_CONCAT(ct.name, ':', c.expiration) AS certificates
FROM people p
LEFT JOIN certs c ON p.id = c.person_id
LEFT JOIN cert_types ct ON c.certification_id = ct.id
GROUP BY p.id;
| id | name | dob | requires_cert | certificates |
|---|---|---|---|---|
| 1 | Person 1 | 2000-01-01 | 1 | Certification 1:2025-01-01,Certification 2:2024-03-30 |
| 2 | Person 2 | 2006-06-06 | 0 | null |
| 3 | Person 3 | 1980-01-01 | 1 | Certification 2:2066-06-06 |