Using GROUP_CONCAT, GROUP, and HAVING in the same statement in MySQL

Question

In an application a have tables projects, categories, and a table, that handles the n:m relationship between of them (project_category_info):

enter image description here

Now I want to get all projects of a category (I'm solving this with HAVING). I also needs the information in the result set, which categories each projects belongs to (CONCAT_GROUP combined with GROUP):

SELECT
    `projects`.*,
    `categories`.`id` AS `id`,
    GROUP_CONCAT(categories.id SEPARATOR '|||') AS `categories`,
    GROUP_CONCAT(categories.short_name SEPARATOR '|||') AS `category_names`
FROM
    `projects`
INNER JOIN
    `project_category_info` ON `project_category_info`.`project_id` = `projects`.`id`
LEFT JOIN
    `categories` ON `project_category_info`.`category_id` = `categories`.`id`
GROUP BY
    `projects`.`id`
HAVING
    (`categories`.`id` = 3)
;

The result set sontains 13 rows. But when I omit GROUP_CONCAT and GROUP, I get one row more. Why? What can cause this behavior?

Can you supply some of the sample data that shows the behavior in an [SQL Fiddle?](http://www.sqlfiddle.com) Also, the above query is wrong. You cannot use `HAVING` for a condition that does not use any aggregate functions. Move that condition to a `WHERE` clause. Perhaps that's the problem? — AdamMc331, Mar 17 '15 at 14:35
This is a bad bad query (two bad's because it has two problems). If your intentions are to have category_id in the result set then it MUST be in your GROUP BY clause. Any other RDBMS would simply error out, but MySQL is insane so it lets you do this nonsense (and it is nonsense). Second, use a WHERE clause instead of HAVING. HAVING is applied after aggregation, and since your aggregation is nonsense (see point 1) then your results are going to be nonsense. — JNevill, Mar 17 '15 at 14:52
@McAdam331 Thank you very much for your comment! I replaced `WHERE` with `HAVING` to filter the data after the gouping. I don't remember, but something was not working with `WHERE`. Now I've tried this with `WHERE` ones again -- and it works! Please post your suggestion as answer. — automatix, Mar 17 '15 at 14:53
@JNevill Thank you for your explanation. I actually didn't want to have a `category_id` in my result set, since every project belongs to multiple categories. — automatix, Mar 17 '15 at 15:01
@McAdam331 & @JNevill `HAVING`: Guys, you're right, `HAVING` should be used for aggregated data. But `GROUP_CONCAT` _is_ an aggregation function. And now I know, why I replaced `WHERE` by `HAVING`: `WHERE` filters the data **before** the grouping. That means, my `GROUP_CONCAT` don't work then and I the column `category_name` contains the same category name for all selected rows. — automatix, Mar 17 '15 at 15:06
@automatix If that didn't solve the problem, I highly recommend building some sample data and demonstrating expected results. — AdamMc331, Mar 17 '15 at 15:08
Additinally to my [comment](http://stackoverflow.com/questions/29101773/using-group-concat-group-and-having-in-the-same-statement-in-mysql?noredirect=1#comment46435198_29101773): The reason, why I had to add `category_id` to the list of the selected rows, is `HAVING`, since without this column I got an error: `Unknown column 'project_category_info.category_id' in 'having clause'`. — automatix, Mar 17 '15 at 15:13
@McAdam331 I built a sample database with some data: http://www.sqlfiddle.com/#!9/d2db1/5 — automatix, Mar 17 '15 at 16:29

AdamMc331 · Accepted Answer · 2015-03-17T15:18:05.273

1

I am beginning to believe your logic is wrong in your query. I recommend trying to rewrite it. The best way is to step back and break the problem down before putting it back together.

To get all projects of a category:

SELECT project_id
FROM project_category_info
WHERE category_id = 3;

Now, join that back to the category_info table to get all rows for those projects:

SELECT *
FROM project_category_info
WHERE project_id IN(
   SELECT project_id
   FROM project_category_info
   WHERE category_id = 3);

You can join that to projects and categories to get the names:

SELECT p.id, p.title, c.title
FROM project_category_info pci
JOIN projects p ON p.id = pci.project_
JOIN categories c ON c.id = pci.catgory_id
WHERE pci.project_id IN(
   SELECT project_id
   FROM project_category_info
   WHERE category_id = 3);

Now, you can add GROUP_CONAT() to the c.title column, and group by p.id:

SELECT p.id, p.title, GROUP_CONCAT(c.short_name SEPARATOR '|||') AS category_names
FROM project_category_info pci
JOIN projects p ON p.id = pci.project_
JOIN categories c ON c.id = pci.catgory_id
WHERE pci.project_id IN(
   SELECT project_id
   FROM project_category_info
   WHERE category_id = 3)
GROUP BY p.id;

edited Mar 17 '15 at 15:18

answered Mar 17 '15 at 14:56

AdamMc331

16,492
10
71
133

It doesn't work as wished, please see my [comment](http://stackoverflow.com/questions/29101773/using-group-concat-group-and-having-in-the-same-statement-in-mysql?noredirect=1#comment46435198_29101773). – automatix Mar 17 '15 at 15:07
@automatix okay, I tried something completely different. – AdamMc331 Mar 17 '15 at 15:25
I edited your question -- formatted it, in order to make it better comparable wit the original code, and fixed some typos. But the changes seem to be declined. – automatix Mar 17 '15 at 16:59
I built a sample database with some data. Unfortunately, our statements ([my original one](http://www.sqlfiddle.com/#!9/d2db1/9) and [yours](http://www.sqlfiddle.com/#!9/d2db1/7)) return the same results . Anyway, I've just tried out your second solution - and it seems to work! Thank you! – automatix Mar 17 '15 at 17:01
@automatix Glad I could help! Feel free to upvote and accept the solution if you use it, so future readers know what solved your problem. – AdamMc331 Mar 17 '15 at 17:06

Using GROUP_CONCAT, GROUP, and HAVING in the same statement in MySQL

1 Answers1