MySQL: Grouping with condition

group byjoin;MySQL

"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  |

Best Answer

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.)

Related Question