T-SQL - get data based on two columns

Question

I want to create the following output from below image:

The input data is from a view (Select * from test). The goal is to get all data where the progress column contains the text tbd and the counter number is 1.

Can this be solved with case when statement?

Since sqlfiddle is not working here's the schema:

CREATE TABLE test
(
    [ID] [int] NOT NULL,
    [Counter] [int] NOT NULL,
    [Name] nvarchar(200) NULL,
    [Progress] nvarchar(200) NOT NULL
)

INSERT INTO test
VALUES (1, 1, 'userA', 'tbd'),
       (1, 2, 'userB', 'done'),
       (1, 3, 'userC', 'tbd'),
       (2, 1, 'userB', 'done'),
       (2, 5, 'userA', 'tbd'),
       (3, 1, 'userD', 'tbd'),
       (3, 2, 'userA', 'done'),
       (3, 7, 'userC', 'tbd'),
       (3, 11, 'userB', 'tbd')

I couldn't get it to work.

Hope you can help me out.

Thanks alot.

Answer 1

Using Exists clause you can achieve your desired result.

Query

SELECT
    *
FROM test t
WHERE EXISTS (SELECT 1 FROM test
            WHERE t.ID = ID
            AND progress = 'tbd'
            AND counter = 1)

Result

ID  Counter  Name    Progress
-----------------------------
1      1     userA   tbd
1      2     userB   done
1      3     userC   tbd
3      1     userD   tbd
3      2     userA   done
3      7     userC   tbd
3      11    userB   tbd

And another alternative solution is simple SELF JOIN like this-

Query

SELECT
    le.ID, le.Counter, le.Name, le.Progress
    FROM test le
INNER JOIN test re ON le.ID = re.ID
WHERE re.progress = 'tbd'
    AND re.counter = 1

Above query returns the same result.