仅选择必须填充两个条件的记录

I have this table:

我有这张桌子:

id type otherid
1   4     1234
2   5     1234
3   4     4321

As you can see there are 3 records, 2 of them belongs to otherid "1234" and got type of 4 and 5.

你可以看到有3条记录,其中2条属于otherid“1234”,并且类型为4和5。

Last record belongs to otherid of "4321" and has only a type of 4.

最后一条记录属于“4321”的otherid,只有4的类型。

I need to select all otherid that got only the type 4 and not the type5.

我需要选择只有类型4而不是类型5的所有otherid。

Example: after this select on that table the query shuould return only the record 3

示例:在该表上选择之后,查询应仅返回记录3

Thanks

add1:

Please consider the TYPE can be any number from 1 up to 20. I only need otherid that got type 4 but not type 5 ( except than that they can have any other type )

请考虑TYPE可以是1到20之间的任何数字。我只需要有类型4而不是类型5的otherid(除了它们可以有任何其他类型)

add2:

using mysql 5.1

使用mysql 5.1

3 个解决方案

#1

Another way is by using a left join from where you exclude rows that have both type 4 and 5:

另一种方法是使用左连接,从中排除同时具有类型4和5的行:

select a.*
from table1 a
    left join table1 b on b.otherid = a.otherid and b.type = 5
where a.type = 4 and b.id is null

#2

This is kind of a workaround

这是一种解决方法

SELECT * FROM (
  SELECT GROUP_CONCAT('|',type,'|') type,other_id FROM table GROUP BY otherid
) t WHERE type LIKE '%|4|%' AND type NOT LIKE '%|5|%'

#3

You could use a not exists subquery:

您可以使用不存在的子查询:

select  distinct otherid
from    YourTable as yt1
where   yt1.type = 4
        and not exists
        (
        select  *
        from    YourTable as yt2
        where   yt1.otherid = yt2.otherid
                and yt1.type <> yt2.type  -- use this line for any difference
                and yt2.type = 5          -- or this line to just exclude 5
        )

#1