MySQL using 3 tables and relations - How do I get all information from one side and names from the other?

Question

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

Answer 1

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.