Complex MySQL table select/join with pre-condition
- by Howard
Hello, I have the schema below
CREATE TABLE `vocabulary` (
`vid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255),
PRIMARY KEY vid (`vid`)
);
CREATE TABLE `term` (
`tid` int(10) unsigned NOT NULL auto_increment,
`vid` int(10) unsigned NOT NULL default '0',
`name` varchar(255),
PRIMARY KEY tid (`tid`)
);
CREATE TABLE `article` (
`aid` int(10) unsigned NOT NULL auto_increment,
`body` text,
PRIMARY KEY aid (`aid`)
);
CREATE TABLE `article_index` (
`nid` int(10) unsigned NOT NULL default '0',
`tid` int(10) unsigned NOT NULL default '0'
)
INSERT INTO `vocabulary` values (1, 'vocabulary 1');
INSERT INTO `vocabulary` values (2, 'vocabulary 2');
INSERT INTO `term` values (1, 1, 'term v1 t1');
INSERT INTO `term` values (2, 1, 'term v1 t2 ');
INSERT INTO `term` values (3, 2, 'term v2 t3');
INSERT INTO `term` values (4, 2, 'term v2 t4');
INSERT INTO `term` values (5, 2, 'term v2 t5');
INSERT INTO `article` values (1, "");
INSERT INTO `article` values (2, "");
INSERT INTO `article` values (3, "");
INSERT INTO `article` values (4, "");
INSERT INTO `article` values (5, "");
INSERT INTO `article_index` values (1, 1);
INSERT INTO `article_index` values (1, 3);
INSERT INTO `article_index` values (2, 2);
INSERT INTO `article_index` values (3, 1);
INSERT INTO `article_index` values (3, 3);
INSERT INTO `article_index` values (4, 3);
INSERT INTO `article_index` values (5, 3);
INSERT INTO `article_index` values (5, 4);
Example. Select term of a defiend vocabulary (with non-zero article index), e.g. vid=2
select a.tid, count(*) as article_count from term t JOIN article_index a
ON t.tid = a.tid where t.vid = 2 group by t.tid;
+-----+---------------+
| tid | article_count |
+-----+---------------+
| 3 | 4 |
| 4 | 1 |
+-----+------------
Question:
Select terms
a. of a defiend vocabulary (with non-zero article index, e.g. vid=1 = term {1,2})
b. given that those terms are linked with articles which are linked with terms under vid=2, e.g. = {1}, term with tid=2 is excluded since no linkage to terms under vid=2
SQL: Any idea?
Expected result:
+-----+---------------+
| tid | article_count |
+-----+---------------+
| 1 | 2 |
+-----+---------------+
