DROP TABLE IF EXISTS `k4_tags`;
CREATE TABLE IF NOT EXISTS `k4_tags` (
`tag_id` int(10) unsigned NOT NULL auto_increment,
`tag_name` varchar(20) NOT NULL,
PRIMARY KEY (`tag_id`),
UNIQUE KEY `tag_name` (`tag_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `k4_thread_tags`;
CREATE TABLE IF NOT EXISTS `k4_thread_tags` (
`thread_id` int(10) unsigned NOT NULL,
`tag_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`thread_id`,`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
----------------------------------------------------
In table k4_tags, there is this:
tag_names: test, foo, bar
tag_ids: 1, 2, 3
----------------------------------------------------
In k4_thread tags, there is this:
thread_ids: 1, 1 | 2, 2 | 3 | 4, 4
tag_ids: 1, 2 | 2, 3 | 2 | 1, 3
There can be multiple tags for each thread. The pipes
are there to separate individual threads.
----------------------------------------------------
Here's what I woould like to be able to do:
Given any tag id, I would like to be able to find all
of the other tags that is is commonly / most used with
in other threads.
So, If I had tag_id 2, I would find tags: 1 and 2
If I had tag_id 3, I would find tags: 1 and 2
If I had tag_id 1, I would find tags: 2 and 3
----------------------------------------------------
Here's the query I'm currently trying. It is only returning
the tag_id that I have (the one in the IN)
SELECT ts. * , COUNT( tt.thread_id ) AS tag_count, tt.tag_id
FROM k4_thread_tags tt
LEFT JOIN k4_tags ts ON ts.tag_id = tt.tag_id
GROUP BY ts.tag_id
HAVING tt.tag_id
IN (
1
)
ORDER BY tag_count DESC
----------------------------------------------------
Thank's for reading!