SELECT only records which must fill two conditions

Question

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.

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

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

Example: after this select on that table the query shuould return only the record 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 )

add2:

using mysql 5.1

Answer 1

Another way is by using a left join from where you exclude rows that have both type 4 and 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

Answer 2

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
        )

Answer 3

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|%'