Select value in group based on every/any conditions I have a table entity_translations, which relate O-&gt;M to string_translations via entity_translation_fields. Each row in string_translations has an additional status that can be obtained via a join with current_string_translation_status.
I need to add an additional column to the result of a select from entity_translations that will be determined based on the following conditions:

If all string_translation records that correspond to an entity_translation record have status = 'READY', then that entity_translation gets status 'READY'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'FAILED', then that entity_translation gets status 'FAILED'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'IN_PROGRESS', then that entity translation gets status 'IN_PROGRESS'.
Else, that entity translation gets status 'ADDED'.

What I want
Given schema and data in this fiddle, I would like to have the following result:
|  id | entity_id | entity_type |      status |
| --- | --------- | ----------- | ----------- |
|   1 |         1 |        post |      FAILED | &lt;--- Because status for string translation 2 is &quot;FAILED&quot;
|   2 |         2 |        post | IN_PROGRESS | &lt;--- Because status for string translation 5 is &quot;IN_PROGRESS&quot;
|   3 |         3 |        post |       READY | &lt;--- Because status for string translation 6 is &quot;READY&quot;


Here's the query I used to get the data that I would like to aggregate into the above desired result:
SELECT *
FROM `entity_translations`
JOIN `entity_translation_fields`
    ON (`entity_translation_fields`.`entity_translation_id` = `entity_translations`.`id`)
JOIN `string_translations`
    ON (`string_translations`.`id` = `entity_translation_fields`.`string_translation_id`)
JOIN `current_string_translation_status`
    ON (`current_string_translation_status`.`string_translation_id` = `string_translations`.`id`)
ORDER BY `entity_translations`.`id` ASC, `string_translations`.`id` ASC;

Markdown tables weren't rendering for some reason, so I just added it as code.
What I tried

One way could be to join entity_translation with string_translation (and its status view), then group by entity_translation's PK. But I don't know how I could then determine the values from the status column that is not in GROUP BY. If ANY() and ALL() were aggregate functions, I could use them in multiple nested IF conditions; but they're not.
Perhaps, another way is to use a subquery to add a column to string_translation. But I don't understand how to then use ANY or ALL with yet another subquery.

Here is the whole setup. It has some extra things that are not relevant to this current problem. The query that is relevant is the one that says the following on top:
-- Get data for all entity translations, including their current status &lt;----- PROBLEM
If I understand the task correctly, then
SELECT subquery.id, 
       subquery.entity_type, 
       subquery.entity_id,
       CASE WHEN SUM(subquery.status != 'READY') = 0 THEN 'READY'
            WHEN SUM(subquery.status = 'FAILED') &gt; 0 THEN 'FAILED'
            WHEN SUM(subquery.status = 'IN PROGRESS') &gt; 0 THEN 'IN PROGRESS'
            ELSE 'ADDED'
            END status
FROM (SELECT et.id, et.entity_type, et.entity_id, stl.status
      FROM entity_translations et
      JOIN entity_translation_fields etf ON etf.entity_translation_id = et.id
      JOIN string_translations st ON etf.string_translation_id = st.id
      JOIN string_translation_log stl ON stl.string_translation_id = st.id
      JOIN (SELECT string_translation_id, MAX(changed) changed
            FROM string_translation_log
            GROUP BY string_translation_id) stl1 ON stl1.string_translation_id = stl.string_translation_id
                                                AND stl1.changed = stl.changed
      ORDER BY et.id, etf.string_translation_id, stl.changed ) subquery
GROUP BY subquery.id, 
         subquery.entity_type, 
         subquery.entity_id;

fiddle