I have the following tables:

User

id | full_name | active
1  | joe       | 1
2  | dave      | 0

Cert_Type

id | cert_name
1  | Cert 1
2  | Cert 2
3  | Cert 3

Cert

id | user_id | cert_type_id | expires
1  | 1       | 1            | 2022-10-24
2  | 2       | 2            | 2021-01-10

My SQL query:

SELECT u.full_name, ct.cert_name, c.expires 
FROM User u
LEFT JOIN Cert 
  ON u.id = c.user_id 
FULL JOIN Cert_Type 
  ON c.cert_type_id = ct.id
WHERE u.active = 1

The results I need:

1. Show the expiry of a Cert associated with a User
2. Contain all Cert Types even if one isn't associated with a User
3. Do not display the expiry for a User who is not active

What I would like my retrieved table to look like:

full_name | cert_name | expires
joe       | Cert 1    | 2022-10-24
NULL      | Cert 2    | NULL
NULL      | Cert 3    | NULL

What is actually retrieved:

full_name | cert_name | expires
joe       | Cert 1    | 2022-10-24
NULL      | Cert 3    | NULL

I think the fact that User 'dave' is not active it's excluding 'Cert 2'.

I'm not sure if I need two separate SELECTs and merge the results?

Any help with this would be greatly appreciated.

Thank you in advance.

CodePudding user response：

Some outer joins and case expressions should get the job done:

select case when "User".active = 1 then full_name end as full_name,
cert_name,
 case when "User".active = 1 then expires end as expires
from "User" full join Cert
on "User".id = Cert.user_id
full join Cert_type
on Cert.cert_type_id = Cert_type.id;

Fiddle