Return result only if all conditions meet-CodePudding

I am stuck with my thoughts and cannot figure out a solution.

I have a table of items with many fields. But the critical for this discussion are ID, STATUS, CATEGORY.

I would like to query this table and return a result only if ALL conditions meet. I do not care how many records are returned, I am just checking if I am getting record(s) results or empty results.

The logic: If ALL Service items are completed and there are Upgrade items that are not completed.

For example the table below

ITEM ID  STATUS     CATEGORY
10       COMPLETED  SERVICE
20       OPEN       SERVICE
30       COMPLETED  UPGRADE

With the case above^ I would like to return 0 records as not all conditions meet.

Another Example

ITEM ID  STATUS     CATEGORY
10       COMPLETED  SERVICE
20       OPEN       SERVICE
30       COMPLETED  UPGRADE
40       OPEN       UPGRADE

With the case above^ I would like to return 0 records as not all conditions meet.

Any idea how I can achieve this? SQL Server 2016/2019

Cheers!

CodePudding user response：

Sounds what you wanted can be achieve using 2 EXISTS conditions

SELECT *
FROM   tbl
WHERE  NOT EXISTS  -- ALL Service items are completed 
       (
           SELECT *
           FROM   tbl 
           WHERE  Category = 'SERVICE'
           AND    Status   <> 'COMPLETED'
       )
AND    EXISTS      -- there are Upgrade items that are not completed
       (
           SELECT *
           FROM   tbl 
           WHERE  Category = 'UPGRADE'
           AND    Status   <> 'COMPLETED'
       )

CodePudding user response：

You can go for simple query, as given below:

SELECT [Item ID], [Status],[Category]
FROM TableName
WHERE Status = 'COMPLETED' AND Category = 'SERVICE'