CODEWITHSUNDEEP
mysqlsql
Piotr
Piotr

Reputation: 197

GROUP_CONCAT and INNER JOIN with WHERE clause

I've got a problem with getting data. I would like to find all courses, which contain the flag in where clause and get all flags course it contains.

Records for the course table:

+----+----------+
| id |   name   |
+----+----------+
| 1  | Spring   |
| 2  | Hibernate|
| 3  | C#       |
+----+----------+

Records for the course_flags table:

+----+----------+----------+
| id |course_id |  flag_id |
+----+----------+----------+
| 1  |     1    |     1    |
| 2  |     1    |     2    |
| 3  |     2    |     1    |
+----+----------+----------+

Records for the flag table:

+----+------------+
| id |    name    |
+----+------------+
| 1  | promocja   |
| 2  |last minute |
+----+------------+

My query

SELECT course.id, course.name,
       GROUP_CONCAT(Flag.id SEPARATOR ',') as flags
FROM course
INNER JOIN course_flags ON 
    course.id = course_flags.course_id
INNER JOIN flag ON 
    flag.id = course_flags.flag_id
WHERE flag.name LIKE 'promocja'
GROUP BY Course.id

Unfortunately my database engine returned records with one flag in flags column:

+----+----------+----------+
| id |   name   |   flags  |
+----+----------+----------+
| 1  | Spring   |     1    |
| 2  | Hibernate|     1    |
+----+----------+----------+

I had hoped to get this result:

+----+----------+----------+
| id |   name   |   flags  |
+----+----------+----------+
| 1  | Spring   |   1,2    |
| 2  | Hibernate|    1     |
+----+----------+----------+

Question how do I get it?

Thanks in advance!

Upvotes: 1

Views: 312

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

The problem with simply using joins like that is that your WHERE clause will reduce the flags available to the result set. What you really need to do is use a subselect to first determine the id's that have this flag, then using that set of id's, determine the flag for each of those id's. that may look like this:

SELECT
    c.id AS id,
    c.name AS name,
    GROUP_CONCAT(cf.flag_id) AS flags
FROM course AS c
INNER JOIN course_flag AS cf
    ON c.id = cf.course_id
INNER JOIN 
    (SELECT cf2.course_id AS id
    FROM course_flags AS cf2
    INNER JOIN flag AS f
    WHERE f.name = ?) AS ids
    ON c.id = ids.id
GROUP BY c.id

Where ? is the flag name you are querying for. Note I also used = here in the WHERE clause instead of LIKE since you weren't even using LIKE functionality (you had no wildcard).

Upvotes: 2

eggyal
eggyal

Reputation: 125865

The WHERE clause restricts records prior to grouping; whereas the HAVING clause restricts results after grouping:

SELECT   course.id, course.name, GROUP_CONCAT(Flag.id) flags
FROM     course
    JOIN course_flags ON course_flags.course_id = course.id
    JOIN flag ON flag.id = course_flags.flag_id
GROUP BY Course.id
HAVING   SUM(flag.name = 'promocja')

See it on sqlfiddle.

Upvotes: 2

Related Questions