SQL where x or y, but not both

Question

I have a table that lists restaurants that serve either beef chicken or something close to this:

Res Meat res1 chicken res1 chicken res1 beef res2 chicken res2 chicken res3 beef 

and I'm trying to write a query that returns which restaurants only provide chicken or beef, but not both, without exception, duplicate results.

Answer 1

Try as follows:

 create table tab ( Res varchar(10), Meat varchar(10) ) insert into tab ( Res, Meat ) values ( 'res1', 'chicken' ) insert into tab ( Res, Meat ) values ( 'res1', 'chicken' ) insert into tab ( Res, Meat ) values ( 'res1', 'beef' ) insert into tab ( Res, Meat ) values ( 'res2', 'chicken' ) insert into tab ( Res, Meat ) values ( 'res2', 'chicken' ) insert into tab ( Res, Meat ) values ( 'res3', 'beef' ) select Res from ( select distinct Res,Meat from tab ) T group by Res having count(1) = 1 

Answer 2

Try the following:

 CREATE TABLE rest ( id int auto_increment primary key, Res varchar(20), Meat varchar(20) ); INSERT INTO rest (Res,Meat) VALUES ('res1','chicken'), ('res1', 'chicken'), ('res1', 'beef'), ("res2", 'chicken'), ("res2", 'chicken'), ( 'res3', 'beef' ) select Res from (select distinct Res,Meat from rest ) test group by Res having count(1) = 1 

SQLFiddle example ,

Answer 3

you can use the except statement

 select * from table where meat = 'chicken' or meat = 'Beer' except select * from table where meat = 'chicken' and meat = 'Beer' 

Answer 4

Try:

 select Res from rest group by Res having count(distinct Meat) = 1