I've a MySql table which contains rows like,

For Example, I am trying to retrieve products which has only country 7 and should not present in other combination. How can I achieve this?

I tried like below:

SELECT * from table_name WHERE country = 7;

but outcome/result contains pid 436,439 and 336. It should only give 439.

CodePudding user response：

You can use not exists for this:

select *
from table_name t 
where t.country = 7
and not exists (
  select * from table_name t2
  where t2.pid = t.pid and t2.pid != t.pid
);

CodePudding user response：

We can use aggregation here:

SELECT pid
FROM yourTable
GROUP BY pid
HAVING SUM(country <> 7) = 0;