Get records with more than one value and at least one of them is zero

Question

Create table #Tbl ( ID int not null, Keyword nvarchar(max) ) Insert into #Tbl Values ('0','Cryptography') Insert into #Tbl Values ('1','Cryptography') Insert into #Tbl Values ('4','Cryptography') Insert into #Tbl Values ('0','SQL') Insert into #Tbl Values ('0','SQL') Insert into #Tbl Values ('3','Cloud Computing') Insert into #Tbl Values ('6','Recursion') Insert into #Tbl Values ('8','Recursion') Insert into #Tbl Values ('0','Universe') Insert into #Tbl Values ('0','Universe') Insert into #Tbl Values ('7','Universe') 

I need to get headers with more than one identifier, and at least one of the IDs is zero.

Thus, the expected result will be:

 Cryptography Universe 

I tried to execute the request below, but could not add the condition "at least one id is zero"

 select Keyword,COUNT(distinct id) from #Tbl group by Keyword having COUNT(distinct id)>1 

How can I continue here? Thank you for your help.

Answer 1

Assuming your identifiers start with 0, below should work

 select Keyword,COUNT(distinct id) from #Tbl group by Keyword having COUNT(distinct id)>1 and MIN(id) = 0 

Answer 2

There are many ways to do this, one example:

 SELECT DISTINCT Keyword FROM #Tbl T WHERE EXISTS (SELECT 1 FROM #Tbl WHERE Keyword = T.Keyword AND ID = 0) AND EXISTS (SELECT 1 FROM #Tbl WHERE Keyword = T.Keyword AND ID != 0) 

Here is sqlfiddle with a demo.

Answer 3

This should do it:

 SELECT Keyword FROM #Tbl WHERE Keyword IN (SELECT DISTINCT Keyword FROM #Tbl WHERE ID = 0) GROUP BY Keyword HAVING COUNT(DISTINCT id) > 1 

Answer 4

Here's another approach:

 SELECT Keyword, COUNT(DISTINCT ID) FROM #Tbl GROUP BY Keyword HAVING COUNT(DISTINCT ID) > ALL (SELECT COUNT(DISTINCT NULLIF(ID, 0)) UNION ALL SELECT 1) ;