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?
>Solution :
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)