I need help with a GROUP_CONCAT problem.
I have the following tables:
Table: entries (e) Table: authors (a)
+-----+-------------+------+-----+------+ +-----+--------+
| eid | name | text | aid | date | | aid | author |
+-----+-------------+------+-----+------+ +-----+--------+
| 1 | 'article 1' | '..' | 2 | .... | | 1 | 'Sue' |
| 2 | 'article 2' | '..' | 1 | .... | | 2 | 'Jim' |
| 3 | 'article 3' | '..' | 2 | .... | | 3 | 'Tom' |
+-----+-------------+------+-----+------+ +-----+--------+
Table: categories (c) Table: entryCategories (x)
+-----+-------+ +-----+-----+
| cid | cat | | eid | cid |
+-----+-------+ +-----+-----+
| 1 | 'PHP' | | 1 | 1 |
| 2 | 'CSS' | | 1 | 2 |
| 3 | 'SQL' | | 1 | 3 |
+-----+-------+ | 2 | 1 |
| 2 | 3 |
| 3 | 2 |
+-----+-----+
I would like to get all the entries (with author name) that are in a certain category, but I'd also like to get a list of all the other category id's each entry is linked to without another query.
This is the code I have so far:
SELECT e.*, a.name, GROUP_CONCAT( c.cid )
FROM entries e
LEFT JOIN authors a
ON a.aid = e.aid
LEFT JOIN entryCategories x
ON x.eid = e.eid
LEFT JOIN categories c
ON x.cid = c.cid
WHERE x.cid = 1
GROUP BY e.eid
... but I can't figure out how to get the GROUP_CONCAT to return a string like this: "1, 2, 3" - which would be the case for entry #1.
Here is an example of what I'd like to get:
+-----+-------------+------+-----+------+--------+-----------+
| eid | name | text | aid | date | author | GROUPS |
+-----+-------------+------+-----+------+--------+-----------+
| 1 | 'article 1' | '..' | 2 | .... | 'Sue' | '1, 2, 3' |
| 2 | 'article 2' | '..' | 1 | .... | 'Jim' | '1, 3' |
+-----+-------------+------+-----+------+--------+-----------+
Any idea how to do this?
