Advanced Mysql Query To Get Master Record If Two Conditions Matches On Different Rows Of Child Records

I was writing a mysql filter query which has a primary table and another table which holds multiple records against each record of primary table (I will call this table child). Am

Solution 1:

Indeed, as AsConfused hinted, you need to two joins to TABLE2 using aliases

-- both of these are tested:

-- find t1 where it has 2and7in t2

    select t1.*
    from table1 t1
    join table2 ov2 on t1.id=ov2.masterid and ov2.optionValue=2join table2 ov7 on t1.id=ov7.masterid and ov7.optionValue=7

-- find t1 where it has 2and7in t2, and no others in t2

select t1.*, ovx.id
    from table1 t1
      join table2 ov2 on t1.id=ov2.masterid and ov2.optionValue=2join table2 ov7 on t1.id=ov7.masterid and ov7.optionValue=7
      LEFT OUTER JOIN table2 ovx on t1.id=ovx.masterid and ovx.optionValue notin (2,7)
    WHERE ovx.id is null

Solution 2:

You can try something like this (no performance guarantees, and assumes you only want exact matches):

select table1.* from table1 join
(select masterid, group_concat(optionvalue orderby optionvalue) as opt from table2 
groupby masterid) table2_group on table1.id=table2_group.masterid
where table2_group.opt='2,7';

http://sqlfiddle.com/#!9/673094/9

Solution 3:

select * from t1 where id in (select masterid from t2 where (t2.masterid in (select masterid from t2 where optionvalue=2)) and (t2.masterid in (select masterid from t2 where optionvalue=7)))

Old school :-) Query took 0.0009 sec.

Solution 4:

This can also be done without the joins using correlated exists subqueries. That may be more efficient.

select*from table1 
 WHEREEXISTS (SELECT1FROM table2 WHERE table1.id=table2.masterid and optionvalue =2)
   ANDEXISTS (SELECT1FROM table2 WHERE table1.id=table2.masterid and optionvalue =7)

If this is to be an exclusive match as suggested by, "when the optionvalue matches only both 2 different conditions match on second table" then you could ad yet a third exists condition. Performance-wise this may start to break down.

ANDNOTEXISTS (SELECT1FROM table2 WHERE table1.id=table2.masterid AND optionvalue  NOTIN (2,7)

Edit: A note on correlated subqueries from Which one is faster: correlated subqueries or join?.