mysql 高级匹配查询,如果两个条件在子记录的不同行上匹配，则高级MySQL查询可获取主记录...

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 trying to write a query which fetches record of primary table based on its values on child table. If the child table condition is one then I will be able to do it simply by joining, but I have 2 conditions which falls on 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 has to return unique master records when the optionvalue matches only both 2 different conditions match on second table.

I wrote query with IN...

select * from table1

left join table2 on table1.id=table2.masterid

where table2.optionvalue IN(2,7) group by table1.id;

This gets me all 3 records because IN is basically checking 'OR', but in my case I should not get 3rd master record because it has values 2,6 (there is no 7). If I write query with 'AND' then am not getting 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 the and will fail as am checking different values on same column. I wanted to write a query which fetches master records which has child records with field optionvalues holds both 2 and 7 on different records.

Any help would be much appreciated.

解决方案

Indeed, as AsConfused hinted, you need to two joins 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