Mysql advanced query to get master record if two conditions match on different rows of child records
I was writing a mysql filter query that has a main table and another table that contains multiple records against each record of the primary table (I will call these child tables).
I am trying to write a query that retrieves a record of a primary table based on its values in a child table. If the condition of the child table is one, then I can do it simply by joining, but I have 2 conditions that fall into the same field.
For ex.
table 1:
id name url
1 XXX http://www.yahoo.com
2 YYY http://www.google.com
3 ZZZ http://www.bing.com
table 2:
id masterid optionvalue
1 1 2
2 1 7
3 2 7
4 2 2
5 3 2
6 3 6
My query should return unique master records when optionvalue
only two other conditions are matched against the second table. I wrote a request with IN ...
select * from table1
left join table2 on table1.id=table2.masterid
where table2.optionvalue IN(2,7) group by table1.id;
This gives me all 3 records because IN basically checks for "OR", but in my case I shouldn't be getting the 3rd main record because it has a value of 2.6 (no 7). If I write a query using "AND", then I don't get any records ...
select * from table1
left join table2 on table1.id=table2.masterid
where table2.optionvalue = 2 and table2.optionvalue = 7;
This will not return records as it will not as I am checking for different values in the same column. I wanted to write a query that retrieves master records that have child records with a field optionvalues
that contains both 2 and 7 in different records.
Any help would be much appreciated.
source to share
Indeed, as AsConfused hinted, you need to add two connections to TABLE2 using aliases
- both of these are tested
:
-- find t1 where it has 2 and 7 in t2
select t1.*
from table1 t1
join table2 ov2 on t1.id=ov2.masterid and ov2.optionValue=2
join table2 ov7 on t1.id=ov7.masterid and ov7.optionValue=7
-- find t1 where it has 2 and 7 in t2, and no others in t2
select t1.*, ovx.id
from table1 t1
join table2 ov2 on t1.id=ov2.masterid and ov2.optionValue=2
join table2 ov7 on t1.id=ov7.masterid and ov7.optionValue=7
LEFT OUTER JOIN table2 ovx on t1.id=ovx.masterid and ovx.optionValue not in (2,7)
WHERE ovx.id is null
source to share
You can try something like this (performance guarantees are not guaranteed, and it is assumed that you only want exact matches):
select table1.* from table1 join
(select masterid, group_concat(optionvalue order by optionvalue) as opt from table2
group by masterid) table2_group on table1.id=table2_group.masterid
where table2_group.opt='2,7';
source to share
It can also be done without joins using correlated existing subqueries. It might be more efficient.
select *
from table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id=table2.masterid and optionvalue = 2)
AND EXISTS (SELECT 1 FROM table2 WHERE table1.id=table2.masterid and optionvalue = 7)
If it should be an exclusive match, as suggested "when the optionvalue only matches two different conditions that match the second table", then you can declare another third condition. In terms of performance, this can start to break down.
AND NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id=table2.masterid AND optionvalue NOT IN (2,7)
Edit: a note on correlated subqueries from Which one is faster: correlated subqueries or join? ...
source to share