Say I have these two tables

table REQUESTS:

ID_REQUEST | USER |

1 | Adam
2 | Ben
3 | Charlie

table REVIEWS:

ID_REVIEW | ID_REQUEST | REVIEW

1 | 1 | APPROVED
2 | 1 | APPROVED
3 | 1 | DENIED
4 | 2 | APPROVED
5 | 2 | APPROVED

I would like to SELECT all my requests, joined to the reviews table, such that the ‘review’ field is DENIED if any DENIED entry exists, APPROVED if only APPROVED exists, or NULL/a third value if no reviews at all.

I’d want my output to be:

ID_REQUEST | USER | REVIEW

1 | Adam | DENIED
2 | Ben | APPROVED
3 | Charlie | NULL (or PENDING)

I figure I want a LEFT JOIN to capture reviews that aren’t present at all. I’m trying to do something like a GROUP BY, though perhaps a CASE is more appropriate. I don’t think COALESCE is what I need here, but I’m happy to be wrong!