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

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?

Answer 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;