我正在编写一个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更多推荐
如果两个条件在子记录的不同行上匹配,则使用高级MySQL查询来获取主记录
发布评论