MySQL How to select value if ANY is present, or otherwise fallback to value if ALL match

Say I have these two tables

table REQUESTS:

table REVIEWS:

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:

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!

This can be done using conditional aggregations :

SELECT ID_REQUEST, USER, CASE WHEN COUNT_DENIED > 0 THEN 'DENIED'
                              WHEN COUNT_APPROVED = TOTAL_REVIEW AND TOTAL_REVIEW > 0 THEN 'APPROVED'
                              ELSE 'PENDING'
                         END AS REVIEW
FROM (
  SELECT rq.ID_REQUEST, rq.USER, 
         COUNT(CASE WHEN REVIEW = 'DENIED' THEN 1 END) AS COUNT_DENIED,
         COUNT(CASE WHEN REVIEW = 'APPROVED' THEN 1 END) AS COUNT_APPROVED,
         COUNT(REVIEW) AS TOTAL_REVIEW
  FROM REQUESTS rq
  LEFT JOIN REVIEWS rv ON rq.ID_REQUEST = rv.ID_REQUEST
  GROUP BY rq.ID_REQUEST, rq.USER
) AS s

Results :

ID_REQUEST  USER    REVIEW
1           Adam    DENIED
2           Ben     APPROVED
3           Charlie PENDING

Demo here