Using: PHP 8
I have three tables. The first two have a relationship and the second two have a relationship. I need data from all three, but some information might not be available in the second table. Also, I need to separate the data from the second table when multiple matches are found in the relation. Is this possible with sql, or should I be thinking in terms of multiple queries and combining them with PHP?
I need All people to be displayed and related certifications' expiration dates.
People
| id | Name | DoB | requires_cert |
|---|---|---|---|
| 1 | Person 1 | 2000-01-01 | 1 |
| 2 | Person 2 | 2006-06-06 | 0 |
| 3 | Person 3 | 1980-01-01 | 1 |
Certifications
| person_id | certification_id | Expiration |
|---|---|---|
| 1 | 1 | 2025-01-01 |
| 3 | 2 | 2066-06-06 |
| 1 | 2 | 2024-03-30 |
Certification Types
| id | certification_name |
|---|---|
| 1 | Certification 1 |
| 2 | Certification 2 |
I've tried so many combinations of joins and unions, and can't figure this out for the life of me.
My desired output would be to display the following table:
| id | Name | DoB | Certification 1 | Certification 2 |
|---|---|---|---|---|
| 1 | Person 1 | 2000-01-01 | 2025-01-01 | 2024-03-30 |
| 2 | Person 2 | 2006-06-06 | n/a | n/a |
| 3 | Person 3 | 1980-01-01 | n/a | 2066-06-06 |
You need to pivot the data but MySQL does not have a pivot function, so we emulate the functionality with conditional aggregation. You would start by getting the list of cert_types that you are interested in and then using that to dynamically build a query that looks like this:
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;
Output:
| 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 |
Alternatively, as suggested by user3783243 in the comments, you could just GROUP_CONCAT the certs:
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;
Output:
| 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 |
Here's a db<>fiddle.