mysql - Having problems with getting a count in a select statement -

i have 5 tables follows:

• files
• tags
• profiles
• j_file_tags
• j_profile_tags

so files can have tags, profiles give access tags.

i put 2 queries following:

1. get list of files specific profile has access (the profile must have access tags file might have)
2. get list of tags profile has access , there @ least file in tag.

what need query 2 count of how many files in tag.

here's table structure , sample data:

create table `files` ( `id`  int(4) not null auto_increment , `filename`  varchar(255) character set utf8 collate utf8_general_ci not null , `empid`  int(4) not null , primary key (`id`) ) engine=innodb default character set=utf8 collate=utf8_general_ci row_format=dynamic;  create table `j_file_tags` ( `id`  int(4) not null auto_increment , `fileid`  int(4) null default null , `tagid`  int(4) null default null , primary key (`id`) ) engine=innodb default character set=utf8 collate=utf8_general_ci row_format=dynamic;  create table `j_profile_tags` ( `id`  int(4) not null auto_increment , `profileid`  int(4) null default null , `tagid`  int(4) null default null , primary key (`id`) ) engine=innodb default character set=utf8 collate=utf8_general_ci row_format=dynamic;  create table `profiles` ( `id`  int(4) not null auto_increment , `profilename`  varchar(255) character set utf8 collate utf8_general_ci null default null , primary key (`id`) ) engine=innodb default character set=utf8 collate=utf8_general_ci row_format=dynamic;  create table `tags` ( `id`  int(4) not null auto_increment , `tagname`  varchar(255) character set utf8 collate utf8_general_ci null default null , primary key (`id`) ) engine=innodb default character set=utf8 collate=utf8_general_ci row_format=dynamic;  insert `files` values ('1', 'fileone', '1'); insert `files` values ('2', 'filetwo', '1'); insert `files` values ('3', 'filethree', '1'); insert `files` values ('4', 'filefour', '2'); insert `files` values ('5', 'filefive', '2'); insert `files` values ('6', 'filesix', '2'); insert `files` values ('7', 'fileseven', '2');  insert `profiles` values ('1', 'profileone'); insert `profiles` values ('2', 'profiletwo'); insert `profiles` values ('3', 'profilethree');  insert `tags` values ('1', 'tagone'); insert `tags` values ('2', 'tagtwo'); insert `tags` values ('3', 'tagthree'); insert `tags` values ('4', 'tagfour'); insert `tags` values ('5', 'tagfive');  insert `j_file_tags` values ('1', '1', '1'); insert `j_file_tags` values ('2', '1', '3'); insert `j_file_tags` values ('3', '2', '1'); insert `j_file_tags` values ('4', '2', '5'); insert `j_file_tags` values ('5', '3', '1'); insert `j_file_tags` values ('6', '3', '3'); insert `j_file_tags` values ('7', '3', '6'); insert `j_file_tags` values ('8', '2', '3'); insert `j_file_tags` values ('9', '4', '1'); insert `j_file_tags` values ('10', '4', '2'); insert `j_file_tags` values ('11', '5', '1'); insert `j_file_tags` values ('12', '5', '6');  insert `j_profile_tags` values ('1', '1', '2'); insert `j_profile_tags` values ('2', '1', '3'); insert `j_profile_tags` values ('3', '1', '4'); insert `j_profile_tags` values ('4', '2', '1'); insert `j_profile_tags` values ('5', '2', '2'); insert `j_profile_tags` values ('6', '2', '3'); insert `j_profile_tags` values ('7', '2', '4'); insert `j_profile_tags` values ('8', '2', '5'); insert `j_profile_tags` values ('9', '1', '1'); insert `j_profile_tags` values ('10', '1', '5'); 

here 2 queries:

/* list of files: limit specific employee , tags use has access  */ select      `files`.`id`,     `files`.`filename`,     `files`.`empid`,     group_concat(concat(`tags`.`id`)  separator ', ') `filetags` `files` left join `j_file_tags` on `j_file_tags`.`fileid` = `files`.`id` left join `tags` on `tags`.`id` = `j_file_tags`.`tagid`     `files`.`empid` = 1     ,     `j_file_tags`.`tagid` in (1,2,3,4,5) group      `files`.`id` having      count(`j_file_tags`.`id`) = (select count(`j_file_tags`.`id`) `j_file_tags` `j_file_tags`.`fileid` = `files`.`id` );  /* second query need */ select      `tags`.`id`,     `tags`.`tagname`,     '1' `filecount` /* need actual count */     `tags` left join `j_file_tags` on `j_file_tags`.`tagid` = `tags`.`id` left join `files` on `files`.`id` = `j_file_tags`.`fileid` left join `j_profile_tags` on `j_profile_tags`.`tagid` = `tags`.`id`     `j_profile_tags`.`profileid` = 1     ,      `files`.`empid` = 1 group     `tags`.`id`; /* filecount column need tagone - 2, tagthree - 2 , tagfive - 1 */ 

in sample data first query returns:

• | 1 | fileone | 1 | 1, 3
• | 2 | filetwo | 1 | 3, 1, 5

the second query returns:

• | 1 | tagone | should return 2
• | 3 | tagthree | should return 2
• | 5 | tagfive| | should return 1

the answer simple having clause have in first query throwing off results because there 3 files tags 1 , 3. suggest removing or figuring out way it. rewrote query below display desired result, not fix all.

select tags.id, tags.tagname, count(fileid) 'filecount' files left join j_file_tags on j_file_tags.fileid = files.id left join tags on tags.id = j_file_tags.tagid files.empid = 1 , j_file_tags.tagid in (1,2,3,4,5) , fileid in (1,2) group tags.id