CODEWITHSUNDEEP
sql-server
jittu
jittu

Reputation: 25

I want to show only those ClaimID where all the status is either success or fail

ClaimID CategoryID Status
1 a success
1 b null
1 c fail
2 a success
2 b success
2 c fail
3 a null
3 b null
3 c null

I want to show only those ClaimID where all the Status is either 'success' or 'fail'.
The result should be 2.

Upvotes: 1

Views: 52

Answers (3)

forpas
forpas

Reputation: 164174

You can GROUP BY ClaimID and set the conditions in the HAVING clause:

SELECT ClaimID
FROM tablename 
GROUP BY ClaimID
HAVING COUNT(*) = COUNT(Status)  -- there are not any nulls

See the demo.
Result:

> | ClaimID |
> | ------: |
> |       2 |

Upvotes: 0

JonWay
JonWay

Reputation: 1735

If I understand your question, the below query should work

create table #YourTable  (ClaimID INT, CategoryID VARCHAR(10) ,[Status]VARCHAR(10))
INSERT INTO #YourTable VALUES
(1,'a', 'success'),(1,'b', null),(1,'c', 'fail'),(2,'a', 'success'),
(2,'b', 'success'),(2,'c', 'fail'),(3,'a', null),(3,'b', null),(3,'c', null)

SELECT
ClaimID,
CategoryID,
[Status]
FROM #YourTable
WHERE [Status] IN ('success','fail')
DROP TABLE #YourTable

Output

ClaimID CategoryID  Status
1         a         success
1         c         fail
2         a         success
2         b         success
2         c         fail

Upvotes: 0

Sergey
Sergey

Reputation: 5225

Could you please try the following

SELECT T.ClaimID
FROM YourTable AS T
WHERE NOT EXISTS
(
   SELECT 1 FROM YourTable AS T2 WHERE T.CliamID=T2.ClaimID AND T2.Status IS NULL
)

Upvotes: 1

Related Questions