Select a row based on two columns one with ID and another one with specific values in a column

sql, sql-server, sql-server-2008

Solution

Try this:

SELECT
  t1.*
FROM table1 AS t1
INNER JOIN
(
  SELECT Acc_id
  FROM table1
  WHERE status IN('Active', 'New')
  GROUP BY Acc_id
  HAVING COUNT(DISTINCT status) = 2
) AS t2 ON t1.Acc_id = t2.Acc_id 
WHERE t1.status IN('Active', 'New');

The `HAVING COUNT(DISTINCT status) = 2` with `WHERE status IN('Active', 'New')` will ensure that the selected `Acc_id` have only two statuses `active` and `new` and no more, then `JOIN` with the original table to get the result of the columns.

- SQL Fiddle Demo

This will give you:

| ACC_ID | NAME | STATUS | VALUE |
|--------|------|--------|-------|
|    202 |  net | Active |     2 |
|    202 |  net |    New |     3 |
|    303 |  com | Active |     1 |
|    303 |  com |    New |     4 |
|    505 |  gov |    New |     2 |
|    505 |  gov | Active |     3 |

Problem

I would like to select rows based on specific column values and a unique id column in SQL The Table I have is as follows ``` Acc_id | Name | Status | value ---------------------------------------- 101 | com | Active | 1 202 | net | Active | 2 202 | net | New | 3 303 | com | Active | 1 303 | com | New | 4 303 | com | Inactive | 2 404 | org | Active | 5 404 | org | Inactive | 6 505 | gov | New | 2 505 | gov | Active | 3 ``` I would like to have the following table as a result ``` Acc_id | Name | Status | value ---------------------------------------- 202 | net | Active | 2 202 | net | New | 3 303 | com | Active | 1 303 | com | New | 4 505 | gov | New | 2 505 | gov | Active | 3 ``` As you see above for the same id from column 'Acc_id' with Column 'Status' only with "New" and "Active" are selected

Original source