Complex MySQL table select/join with pre-condition

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` (
`aid` 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, 1);
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 |             3 |
    |   4 |             1 |
    +-----+---------------+

Question:

1. Select terms

   a. of a defiend vocabulary (开发者_Go百科with non-zero article index, e.g. vid=1 => term {1,2})

   b. filter (a), only need those terms which are linked with articles of a specific term, e.g. tid=3, result => {1}, since term with tid=2 is excluded since no linkage to terms of tid=3

SQL: Not correct as the article_count is wrong

SELECT t.tid, count(*) as c FROM term t JOIN article_index i ON t.tid = i.tid WHERE t.vid = 1 AND i.aid IN 

    ( SELECT i.aid FROM term t JOIN article_index i ON t.tid = i.tid WHERE i.tid = 3) 

GROUP BY t.tid ;

Result:

+-----+---------------+
| tid | article_count |
+-----+---------------+
|   1 |             2 |
+-----+---------------+

Expected result: Since only one article linked with both tid=3 and tid=1

+-----+---------------+
| tid | article_count |
+-----+---------------+
|   1 |             1 |
+-----+---------------+

Its not great but its easy to get to

SELECT tid, count(*)
FROM 

(


SELECT distinct t.tid
FROM term t 
JOIN article_index i 
ON t.tid = i.tid 
WHERE t.vid = 1 
AND i.aid IN 

    ( SELECT i.aid 
      FROM term t 
          JOIN article_index i 
          ON t.tid = i.tid 
      WHERE i.tid = 3) 

) t
GROUP BY tid

you just need to replace count(*) to count(distinct t.vid) ...