mysql> describe note;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| code | varchar(30) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> describe tag;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| tagname | varchar(25) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from note;
+----+--------+
| id | code |
+----+--------+
| 2 | Note A |
| 3 | Note A |
| 4 | Note A |
| 5 | Note A |
| 6 | Note A |
| 7 | Note A |
| 8 | Note B |
| 9 | Note B |
| 10 | Note B |
+----+--------+
9 rows in set (0.00 sec)
The Setup
mysql> select * from tag;
+----+---------+
| id | tagname |
+----+---------+
| 1 | tag1 |
| 2 | tag1 |
| 2 | tag2 |
| 3 | tag3 |
| 4 | tag1 |
| 4 | tag2 |
| 5 | tag3 |
| 6 | tag1 |
| 7 | tag2 |
| 8 | tag1 |
| 8 | tag2 |
| 8 | tag3 |
| 9 | tag1 |
+----+---------+
13 rows in set (0.00 sec)
A case of notes, having tage, so each note's tags are tag.id=note.id. I am trying to group count notes with both the same code and the same tags.
select code, group_concat( tagname order by tagname separator ', ') as tags from note, tag where note.id=tag.id group by tag.id order by code;
Yields
+--------+------------------+
| code | tags |
+--------+------------------+
| Note A | tag1, tag2 |
| Note A | tag3 |
| Note A | tag1, tag2 |
| Note A | tag3 |
| Note A | tag1 |
| Note A | tag2 |
| Note B | tag1, tag2, tag3 |
| Note B | tag1 |
+--------+------------------+
which is *close*, but I need a count field and lines with both the same code and 'tags' grouped