sql - MySQL Select where multiple rows in joined table -

let's have 2 tables, like:

+-----------------------------+ |a                            | +------+-----------+----------+ | a_id | firstname | lastname | +------+-----------+----------+ |    1 | tom       | cruise   | |    3 | matt      | damon    | |    4 | ben       | affleck  | |    8 | ryan      | gosling  | +------+-----------+----------+  +-----------------------+ | b                     | +------+------+---------+ | b_id | a_id | b_value | +------+------+---------+ |    2 |    3 | 1     | |    1 |    3 | 2     | |    4 |    8 | 3   | |    8 |    1 | 4    | +------+------+---------+ 

i able toselect rows a meet set of criteria b, there might multiple rows.

for example:

• get first , last name(s) of has b_value of one, , other b_value of two. in example data, refers matt damon.

• get first , last name(s) of has b_value of three, or b_value of four. in example data, ryan gosling has b_value of three, , tom cruise has b_value of four, both ryan gosling , tom cruise returned.

• get first , last name(s) of has either:

  • a b_value of 1 , b_value of two.
  • a b_value of three.

in example data, matt damon meets first condition, , ryan gosling meets second condition, both returned.

the difficulty comes when these conditions complex, combining multiple and/or conditions, , variable in length.

my current attempt (solving example 3 in case), using aggregate functions in having:

select firstname, lastname  temp_a inner join temp_b on temp_a.a_id = temp_b.a_id  group temp_a.a_id  having (sum(b_value="one") , sum(b_value="two")) or sum(b_value="three"); 

which gets correct rows, there's 2 immediate problems approach me:

• sum seems wrong , complicated. i'd have find each of conditionals , make sure each 1 wrapped in sum, of there might lots.
• the b_value indexed, , b has lots of rows. having clause doesn't use index , query noticeably slow.

is there different approach should taking sort of query, bearing in mind i'm not in direct control of conditionals used filtering.

drop table if exists a,b; create table a( a_id int, firstname varchar(20), lastname varchar(20)); insert values (    1 , 'tom'       , 'cruise' ),   (    3 , 'matt'      , 'damon'  ),   (    3 , 'ben'       , 'affleck'),   (    8 , 'ryan'      , 'gosling');    create table b( b_id int, a_id int, b_value varchar(20)); insert b values (    2 ,    3 , 'one'  ),    (    1 ,    3 , 'two'  ),    (    4 ,    8 , 'three'),    (    8 ,    1 , 'four' );   select firstname, lastname,s.vals join ( select b.a_id, group_concat(b_value) vals b group b.a_id ) s on a.a_id = s.a_id  s.vals in ('one,two') or s.vals in ('three')  +-----------+----------+---------+ | firstname | lastname | vals    | +-----------+----------+---------+ | matt      | damon    | one,two | | ben       | affleck  | one,two | | ryan      | gosling  | 3   | +-----------+----------+---------+ 3 rows in set (0.02 sec)