CODEWITHSUNDEEP
sqlsql-server
Maple Li
Maple Li

Reputation: 43

sql get column value which all values in another column no value

I have a database table which contains 4 columns:

column A - must has value and allow duplicate
column B - may be empty
column C - may be empty
column D - must has unique value

example:

  A  |  B  |   C  |  D
  ---|-----|------|-------    
  a1 | b1  |   -  | 101  
  a1 | -   |  c1  | 102
  a1 | b2  |  -   | 103
  a2 | -   |  -   | 104 
  a2 | b3  |  -   | 105   
  a3 | b4  |  c2  | 106
  a3 | -   |  c3  | 107
  a3 | -   |  c4  | 108

What I want is get column A and column B value where for the value in column A, all records in column C no value.

In the example I want to get the following result:

 A    |    B 
------------- 
 a2   |
 a2   |    b3

Upvotes: 2

Views: 1874

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Can you try this?

DECLARE @t TABLE
    (
      A CHAR(2) ,
      B CHAR(2) ,
      C CHAR(2) ,
      D INT
    )

INSERT  INTO @t
VALUES  ( 'a1', 'b1', NULL, 101 ),
        ( 'a1', NULL, 'c1', 102 ),
        ( 'a1', 'b2', NULL, 103 ),
        ( 'a2', NULL, NULL, 104 ),
        ( 'a2', 'b3', NULL, 105 ),
        ( 'a3', 'b4', 'c2', 106 ),
        ( 'a3', NULL, 'c3', 107 ),
        ( 'a3', NULL, 'c4', 108 );
WITH    cte
          AS ( SELECT   t1.* ,
                        RANK() OVER ( PARTITION BY t1.A ORDER BY t1.C ) AS R
               FROM     @t t1
                        JOIN @t t2 ON t2.A = t1.A
             )
    SELECT  t.A ,
            t.B
    FROM    cte
            JOIN @t t ON t.A = cte.A
    GROUP BY t.A ,
            t.B
    HAVING  COUNT(DISTINCT ( R )) = 1

Output:

A   B
a2  NULL
a2  b3

Upvotes: 0

jarlh
jarlh

Reputation: 44795

Sorry, you mean all a2 values because no a2 row has a C value?

select a, b
from tablename t1
where not exists (select 1 from tablename t2
                  where t1.a = t2.a
                  and c is not null)

Upvotes: 3

Related Questions