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

Solution 1:

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
GROUPBY Course.id
HAVING   SUM(flag.name = 'promocja')

See it on sqlfiddle.

Solution 2:

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