infamoustrey - 8 months ago 38

SQL Question

I'm certain that this is a simple WHERE clause. I'm just not sure how to write it.

Let's say I have the following data where a,b,c(numbers) and d(varchar):

What I'd like to do is select rows that have the following conditions:

- Column b must be the same as at least one other row.
- Column c must

have at least one row with a value and one without or one with 0 as a

value.

I'd like to pull in col a and d. But only if b and c meet those conditions.

For example the green data is something I'd like to select, and the red is obviously data I don't care about. Thanks in advance.

Answer

This should do it:

SAMPLE DATA:

```
IF OBJECT_ID('tempdb..#TABLE') IS NOT NULL
DROP TABLE #TABLE
CREATE TABLE #TABLE (A int, B int , C int)
INSERT INTO #TABLE ( A, B , C )
VALUES
(0,1,0),
(1,1,253),
(2,4,800),
(3,5,460),
(4,6,300),
(5,7,350),
(6,7,450)
```

QUERY:

```
;WITH CTE --<-- this is a common table expression, it is essentially the same as having temp tables but it is in memory. Just google CTE tSQL and you will find out more about it.
AS (SELECT *
, rn = ROW_NUMBER() OVER(PARTITION BY B ORDER BY A) --this assigns a row number to the rows that are partitioned by the values in B.
FROM #TABLE)
SELECT A
, B
, C
FROM CTE
WHERE B IN
(SELECT B --<-- here filter to only return the rows that have an rn (row number) greater than 1 meaning there are more than one row for the given value in B
FROM CTE
WHERE rn > 1) AND B IN (SELECT B --<-- here we filter further to ensure at least 1 record has the value 0.
FROM CTE
WHERE C = 0 AND B IN (SELECT B --<--this is to ensure that at least there is one value that is not 0 to handle the bug pointed out by Jay.
FROM CTE
WHERE C <> 0));
```

RESULTS:

More about CTE's

More about ROW_NUMBER()

Source (Stackoverflow)