Can't find logiq mysql request SELECT ... IN -

i have table "posts_tags" 2 fields.

post_id  tag_id 1          1 1          4 1          9 2          1 2          4 2          7 3          2 3          4 

a table "tags" 3 types

tag_id    type       name 1         geoloc     paris 2         geloc      rome 3         geoloc     london 4         paint      abby 5         paint      martin 6         paint      dupont 7         designer   paulo 8         designer   stefy 9         designer   michel 

i post_id linked several tags_id.

i have made simple request posts id following tags : paris, rome.

 $arrray_in = array(1, 2); //this array generated , can contain tags ids, example value, maybe can 10 values or more... select * posts_tags tag_id in($array_in) group post_id 

i able post_id following tags paris (geoloc) , abby (paint). request don't give me result (return post_id : 1, 2, 3 , want post_id : 1, 2)

$arrray_in = array(1, 2); //this array generated , can contain tags ids, example value, maybe can 10 values or more...     select * posts_tags tag_id in($array_in) group post_id 

you can use following posts have tags:

select pt.post_id posts_tags pt pt.tag_id in(1, 4)  group pt.post_id having count(distinct pt.tag_id) =2; 

see sql fiddle demo.

if want return more details posts, canuse:

select * posts_tags pt1 inner join tags t   on pt1.tag_id = t.tag_id   , t.tag_id in (1, 4) exists (select pt.post_id               posts_tags pt               pt.tag_id in(1, 4)                  , pt1.post_id = pt.post_id               group post_id               having count(distinct pt.tag_id) =2); 

see sql fiddle demo