infamoustrey infamoustrey - 1 month ago 8
SQL Question

SQL Query: How to Select across rows and columns

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):

enter image description here

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:

enter image description here

More about CTE's

More about ROW_NUMBER()