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.00 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 |
+----+--------+
| 1 | Note A |
| 2 | Note A |
| 3 | Note A |
| 4 | Note A |
| 5 | Note A |
| 6 | Note B |
| 7 | Note B |
| 8 | Note B |
| 9 | Note B |
+----+--------+
9 rows in set (0.00 sec)
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)
Details:
Notes have a general grouping code. Within the group, each note can have a different combination of tags associated.
Goal: show a summary count of the different combinations of tags for a note, combination only valid for the same note.code. Ideally it should provide a reference note.id on the summary line so you know what is being counted. in example:
note.id count
------- --------
1 1 <just id=1 has 'tag1'>
2 2 <both id=2 and id=4 have tags 'tag1' and 'tag2'>
3 2 <both id=3 and id=5 have tags 'tag3'>
6 2 <both id=6 and id=9 have tag 'tag1' NOTE: these are grouped because 1&2 are code 'Note A' and these are code 'Note B'
7 1 <just id=7 has 'tag2'>
8 1 <just id=8 has 'tag1', 'tag2' and 'tag3'>