如果两个条件在子记录的不同行上匹配，则使用高级MySQL查询来获取主记录

本文介绍了如果两个条件在子记录的不同行上匹配，则使用高级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试图编写一个查询，该查询根据子表上的值获取主表的记录.如果子表条件为1，那么我将可以简单地通过加入来完成，但是我有2个条件属于同一字段.

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 www.yahoo 2 YYY www.google 3 ZZZ www.bing table 2: id masterid optionvalue 1 1 2 2 1 7 3 2 7 4 2 2 5 3 2 6 3 6

当optionvalue仅匹配第二个表上的两个不同条件时，我的查询必须返回唯一的主记录. 我用IN编写查询...

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;

这使我获得了全部3条记录，因为IN基本上是在检查"OR"，但在我的情况下，我不应获得第3条主记录，因为它的值是2,6(没有7).如果我用"AND"写查询，则没有任何记录...

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;

这不会返回的记录，并且会因为检查同一列上的不同值而失败.我想编写一个查询，以获取具有子记录的主记录，该子记录的字段optionvalues将2和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.

推荐答案

确实，正如AsConfused所暗示的那样，您需要使用别名对TABLE2进行两次联接

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