MySQL Select where value = 9 'and' value =3 -

MySQL Select where value = 9 'and' value =3 -

i'm trying select table:

group_id user_id 3 3 9 3 3 18 9 18 9 39 7 39

the user_id has both values 3 , 9:

what want:

group_id user_id 3 3 9 3 3 18 9 18

what tried:

select * prlg_pp_group_members group_id in (3,9); group_id user_id 3 3 9 3 3 18 9 18 9 39

thank you

i explain thought process go along here:

i first wrote query pulled rows group_id of 3 or 9 , grouped them group_id , user_id. made sure there no duplicate rows (like 3,3 appearing twice.)

that gave this:

select * mytable group_id = 9 or group_id = 3 grouping group_id, user_id;

then, can grouping query user_id on status user_id appears twice (that means has group_id of 3 , 9 because we've filtered on conditions.) in specific example, (9, 39) won't appear in final result set because 39 not occur twice.

the query looks this:

select user_id from(select * mytable group_id = 9 or group_id = 3 grouping group_id, user_id ) t grouping user_id having count(*) = 2;

now here's preference comes in: query returns user_id value. not show group_id value, know that value can both 3 , 9 subquery. if want value, you'd have go original table , filter 3 or 9 , check user_id in subquery:

select * mytable (group_id = 9 or group_id = 3) , user_id in(select user_id (select * mytable group_id = 9 or group_id = 3 grouping group_id, user_id ) t grouping user_id having count(*) = 2);

mysql