Retrieve single identifier entries only from others

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 all the records that belong to only 1 to not be different identifiers.

ie o / p - 35.46

I do not need oid = 43 bcz, it also belongs to 2.

I don’t know how to write my question in a table on stackoverflow, so please ignore my wrong query method.

thanks

Answer 1

Try the following:

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

Answer 2

here is 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 ║ ╚═════╝ 

Answer 3

 select count(oid), oid from table where id = 1 group by oid having count(oid) = 1 

Answer 4

 SELECT * FROM TABLE_NAME WHERE id = 1 AND oid NOT IN (SELECT DISTINCT oid FROM TABLE_NAME where id != 1)