MySQL: Grouping with condition

Question

"persons" table:

id | name
-----------
1  | alice
2  | ben
3  | claire

"items" table:

id | name        | person_id | is_active
-----------------------------------------
1  | apple       | 1         | NO
2  | banana      | 2         | YES
3  | carrot      | 2         | NO
4  | dragonfruit | 3         | NO
5  | eggplant    | 3         | YES

So by running the below query:

SELECT * FROM persons p
LEFT JOIN items i ON p.id = i.person_id

I get:

id | name   | id | name        | person_id | is_active
-----------------------------------------------------
1  | alice  | 1  | apple       | 1         | NO
2  | ben    | 2  | banana      | 2         | YES
2  | ben    | 3  | carrot      | 2         | NO
3  | claire | 4  | dragonfruit | 3         | NO
3  | claire | 5  | eggplant    | 3         | YES

Rules in application-level as follows:

• Each person must have at least 1 item.
• Each person can have 0 or 1 active items only.

I want to filter the above result to get:

1. Get 1 record only for each person.
2. Get the row with is_active = "YES" if person has an active item. If person doesn't have any active item, get 1 record (any record) of that person.
3. Order result by person id DESC.

So I'm trying to get something like this:

id | name   | id | name        | person_id | is_active
-----------------------------------------------------
3  | claire | 5  | eggplant    | 3         | YES
2  | ben    | 2  | banana      | 2         | YES
1  | alice  | 1  | apple       | 1         | NO

EDIT:

I need to get the persons records (even ones without an active item). From the items table I need only the active item's name. If the person doesn't have an active item, it's fine if I can get it as empty string or null. So something like this will also be ok:

id | name   | item_name
-----------------------------------------------------
3  | claire | eggplant
2  | ben    | banana
1  | alice  | 

Answer 1

Thanks for the clear specifications of the requirements, and the sample input and output.

"Each person must have at least 1 item." -- probably no extra code.

"Each person can have 0 or 1 active items only." -- GROUP BY person_id, then do the test in HAVING SUM(is_active = 'YES') <= 1.

"Get 1 record only for each person" -- The GROUP BY does that.

"Get the row with is_active = "YES" if person has an active item. If person doesn't have any active item, get 1 record (any record) of that person." -- This gets tricky. If there were only one person, something like this should work: ORDER BY is_active = 'YES' DESC LIMIT 1. But that does not work with grouping. More in a minute.

"Order result by person id DESC" -- This happens at the end.

Now it gets tricky because you need a "groupwise max". Let's try to get the item.id values rather than just the one id desired. Then work on getting the details for the 'first' id. And let's wait until the end to get the person.name; we can easily ignore it until then.

SELECT  id,
        GROUP_CONCAT(id ORDER BY is_active = 'YES' DESC) AS ids
    FROM  items
    GROUP BY  id
    HAVING  SUM(is_active = 'YES') <= 1
    ORDER BY id DESC

See if that gives you the correct set of rows (1 per person, ordered correctly) and the first id of ids is the desired item. Assuming all that works, let's finish by getting the other desired columns. (I will eliminate the redundant column in your sample output, and toss the ids.)

SELECT  p.id,
        p.name,
        IF(i.is_active = 'YES', i.name, '') AS item_name
    FROM (
            the-above-select
         ) AS x
    JOIN items AS i  ON i.id = SUBSTRING_INDEX(x.ids, ',', 1)
    JOIN persons AS p ON p.id = x.id
    ORDER BY p.id DESC

(There is, of course, a chance that I goofed. But that could come close.)

Answer 2

Thanks to @RickJames for the answer and explanation. I'm posting this answer as future reference to myself and anyone else, which seems shorter/simpler and solves the issue:

SELECT 
p.*,
GROUP_CONCAT(IF(i.is_active="YES", i.name, '') SEPARATOR '') as item_name
FROM persons p
LEFT JOIN items i ON p.id = i.person_id
GROUP BY p.id
ORDER BY p.id DESC

Explanation: Since we have only one active item (is_active = "YES") per person, the line GROUP_CONCAT(IF(i.is_active="YES", i.name, '') SEPARATOR '') as item_name and the GROUP BY p.id will do the trick.

• GROUP_CONCAT(... SEPARATOR '') will combine all the person's item names in 1 new column (item_name). e.g. "claire" will have "dragonfruiteggplant" (separated by empty string that's why they're attached), but..
• IF(i.is_active="YES", i.name, '') will append only the active item name, while inactive items will be appended as empty string (which means they will not show up), resulting in the desired output. e.g. "claire" will have "eggplant". "alice" don't have any active item, so she will get empty string.
• GROUP BY p.id will make sure we get only 1 record per person.