To check if group of records contain a boolean in SQL

Question

So I have this table and in SQL as below

Table : Fruits

Id = int, Name = varchar, Type = varchar, isPrimary = bit

Id	Name	Type	isPrimary
1	Apple	Type A	1
2	Apple	Type B	0
3	Mango	Type A	0
4	Mango	Type B	0
5	Mango	Type C	1
6	Mango	Type D	0
7	Orange	Type A	0
8	Orange	Type B	0
9	Orange	Type C	0
10	Orange	Type D	0

I need to get records where a group does not contain isPrimary =1. So in the above case, Orange is the record that I will get.

This is my query, but this is not working as expected.

Select Name from fruits
Group by Name
having isPrimary != 1

How do I go about fixing it?

Answer 1

You can use a subquery. First, we get the name where isprimary=1 in the "inner query," then we excise the name from the outer query. or you can use the left join with the same table to get the same result.

-- using left join
select distinct a.name from #u a
left join
(
select * from #u where Isprimary=1
)b on a.name=b.name 
where b.name is null

-- using subquery
 select distinct name from #u  where name not in (select name from #u where Isprimary=1)