mysql - SQL COUNT with 2 INNER JOINS -

video table stores id , video data.

tag table stores id , tag_name.

video_tag table connects video_ids , tag_ids represent video belongs tag.

for example in query below, can videos belong tags ids both 3 , 4

also, want know how many rows there. how should modify query?

select *             video             inner join video_tag on video.id = video_tag.video_id             inner join tag on tag.id = video_tag.tag_id             video_tag.tag_id in (3,4)             group video.id             having count(video.id)=2             order video.id desc  

---

*

table structures:

   --     -- table structure table `video`     --      create table if not exists `video` (       `id` int(11) not null auto_increment,       `original_id` varchar(20) collate utf8_turkish_ci not null comment 'alınan sitedeki id''si',       `source` tinyint(2) not null,       `title` varchar(160) collate utf8_turkish_ci not null,       `link` varchar(250) collate utf8_turkish_ci not null,       `image` varchar(300) collate utf8_turkish_ci not null,       `seconds` smallint(6) not null,       `fullscreen` varchar(100) collate utf8_turkish_ci not null,       primary key (`id`),       key `source` (`source`,`seconds`)     ) engine=myisam  default charset=utf8 collate=utf8_turkish_ci auto_increment=122987 ;  -- -- table structure table `tag` --  create table if not exists `tag` (   `id` int(11) not null auto_increment,   `tag_name` varchar(24) collate utf8_turkish_ci not null,   primary key (`id`),   unique key `tag_name` (`tag_name`) ) engine=myisam  default charset=utf8 collate=utf8_turkish_ci auto_increment=191 ; -- -- table structure table `video_tag` --  create table if not exists `video_tag` (   `video_id` int(11) not null,   `tag_id` int(11) not null,   key `video_id` (`video_id`,`tag_id`) ) engine=myisam default charset=latin1; 

your query should doing want. but, can simplify it:

select v.* video v inner join      video_tag vt      on v.id = vt.video_id vt.tag_id in (3, 4) group v.id having count(v.id) = 2 order v.id desc ; 

the time not work when video can have duplicate tags of same type. in case, case can instead use count(distinct) instead.

if want return query number of rows for, say, pagination, use sql_calc_found_rows:

select sql_calc_found_rows v.* . . . 

then use found_rows().

if want number of rows, can use subquery, , further simplification:

select count(*) (select v.*       video_tag vt       vt.tag_id in (3, 4)       group vt.id       having count(*) = 2      ) t