Select only the values that have one or more specific value in SQL Server

Question

I have the following table called CampaingControl:

+----------------+-----+-------------+--------------+-----------+--------+
| id_vehicleCamp | vin | id_campaign | id_workorder | id_client | status |
+----------------+-----+-------------+--------------+-----------+--------+
|                |     |             |              |           |        |
+----------------+-----+-------------+--------------+-----------+--------+
|                |     |             |              |           |        |
+----------------+-----+-------------+--------------+-----------+--------+

The status column have only two values APPLIED or PENDING.

What i need to do is show all the distinct values ​​in the column id_campaign having one or more status values ​​equals to PENDING.

For example, i have this data in the table:

+----------------+-------+-------------+--------------+-----------+---------+
| id_vehicleCamp |  vin  | id_campaign | id_workorder | id_client |  status |
+----------------+-------+-------------+--------------+-----------+---------+
|        1       | 43006 |      1      |   OT-001-16  |    NULL   | PENDING |
+----------------+-------+-------------+--------------+-----------+---------+
|        2       | 43010 |      1      |   OT-002-16  |     2     | PENDING |
+----------------+-------+-------------+--------------+-----------+---------+
|        3       | 43009 |      1      |   OT-002-16  |    NULL   | APPLIED |
+----------------+-------+-------------+--------------+-----------+---------+
|        4       | 43008 |      2      |   OT-002-16  |    NULL   | APPLIED |
+----------------+-------+-------------+--------------+-----------+---------+
|        5       | 43002 |      3      |   OT-005-16  |     4     | PENDING |
+----------------+-------+-------------+--------------+-----------+---------+

The final output needs to be:

+-------------------+
| Pending Campaings |
+-------------------+
|         1         |
+-------------------+
|         3         |
+-------------------+

The campaing with value 1 have one APPLIED status, but have 2 with PENDING status, then isn't completed.

The campaing with value 3 only have one record and have PENDING status, then isn't completed.

Answer 1

Use conditional aggregation:

SELECT id_campaign
FROM CampaingControl
GROUP BY id_campaign
HAVING SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) >= 1

Note that this query will generalize to other questions you might have, such as how many campaigns have two pending entries but only one applied entry.