How to get non duplicate values

Question

Hi All;

Below is my  table, in which one client can go on multiple programmes

 i need to get those clients which are on programme Boss and not NEA

Below should be the output

Any help much appreciated

Thanks

---

Pradnya07

Answer 1

Try:

SELECT [Client Name], [Programme Name]
FROM Table t1
WHERE [Programme Name] = 'Boss'
AND NOT EXISTS
(
 SELECT 1
 FROM Table
 WHERE [Programme Name] = 'NEA'
   AND [Client Name] = t1.[Client Name]
);

Answer 2

SELECT 
      [ClientName]
      ,[programme]
  FROM yourtable
  where programme = 'Boss'
  and clientName not in
  (
  SELECT 
      [ClientName]    
  FROM yourtable
  where programme = 'nea'
  
  )

Answer 3

With cte As
(Select ClientName From YourTable Where ProgramName = 'Boss'
Except
Select ClientName From YourTable Where ProgramName = 'NEA')
Select ClientName, 'Boss' As ProgramName
From cte;

Tom

Answer 4

Try

with cte as
(select [Client name],count(distinct [Programme name]) cnt
from tab1
group by [Client name]
having count(distinct [Programme name])=1)
select [Client name],[Programme name]
from tab1
where [Client name] in(select [Client name]
from cte);

---

Many Thanks & Best Regards, Hua Min

Answer 5

thanks all it worked  like a charm

---

Pradnya07