Fetch records of single id only not of any other

Question

I have a table like this...

oid      id 
35       1  
43       1  
46       1 
43       2  
49       2

I have id=1 now I want pnly those records which belong to only 1 not any other ids.

i.e o/p - 35,46

I dont need oid = 43 bcz it is belonging to 2 also.

I dont know how to write my question in table on stackoverflow so Please ignore my wrong way of asking.

thanks

Answer 1

Try below:

SELECT * FROM `table`
WHERE id = 1 AND oid NOT IN (SELECT oid FROM `table` where id != 1)

Answer 2

here's another way,

SELECT  oid
FROM    tableName
GROUP   BY oid
HAVING  COUNT(DISTINCT id) = 1 AND  -- counts the number of ID for OID
        MAX(ID) = 1                 -- checks if the value of ID is equal to 1

• SQLFiddle Demo

OUTPUT

╔═════╗
║ OID ║
╠═════╣
║  35 ║
║  46 ║
╚═════╝