CODEWITHSUNDEEP
sqlt-sql
90abyss
90abyss

Reputation: 7347

SQL - Select from column A based on values in column B

Lets say I have a table with 2 columns (a, b) with following values:

 a     b
---   ---
 1     5
 1    NULL 
 2    NULL
 2    NULL
 3    NULL

My desired output: 

 a
---
 2
 3

I want to select only those distinct values from column a for which every single occurrence of this value has NULL in column b. Therefore from my desired output, "1" won't come in because there is a "5" in column b even though there is a NULL for the 2nd occurrence of "1".

How can I do this using a TSQL query?

Upvotes: 1

Views: 2992

Answers (6)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Two more ways to do this:

SELECT a
FROM t

EXCEPT

SELECT a
FROM t
WHERE b IS NOT NULL ;

This would use an index on (a, b):

SELECT a
FROM t
GROUP BY a
WHERE MIN(b) IS NOT NULL ;

Upvotes: 2

A. El-Helw
A. El-Helw

Reputation: 201

This should do it:

SELECT DISTINCT a
FROM t
WHERE b IS NULL
AND a NOT IN (SELECT a FROM t WHERE b IS NOT NULL);

Upvotes: 0

Turophile
Turophile

Reputation: 3405

To turn your question on it's head, you want the values from column a where there are no non-null values for that value in column b.

select distinct a
from table1 as t1
where 0 = (select count(*) 
           from table1 as t2
           where t1.a = t2.a
           and b is not null)

Sample fiddle is here: http://sqlfiddle.com/#!6/5d1b8/1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If I understand correctly, you can do this with group by and having:

select a
from t
group by a
having count(b) = 0;

When you use count() with a column name, it counts the number of non-NULL values. Hence, if all values are NULL, then the value will be zero.

Upvotes: 6

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this:

DECLARE @tbl TABLE(a INT, b INT);
INSERT INTO @tbl VALUES(1,5),(1,NULL),(2,NULL),(2,NULL),(3,NULL);
--Your test data
SELECT * FROM @tbl;

--And this is what you want - hopefully...
SELECT DISTINCT tbl.a
FROM @tbl AS tbl
WHERE NOT EXISTS(SELECT * FROM @tbl AS x WHERE x.a=tbl.a AND b IS NOT NULL)

Upvotes: 1

Amit
Amit

Reputation: 46323

It's fairly simple to do:

SELECT A
FROM table1
GROUP BY A
HAVING COUNT(B) = 0

Grouping by A results in all the rows where the value of A is identical to be transferred into a single row in the output. Adding the HAVING clause enables to filter those grouped rows with an aggregate function. COUNT doesn't count NULL values, so when it's 0, there are no other values in B.

Upvotes: 2

Related Questions