CODEWITHSUNDEEP
sqlsql-servergroup-byhaving-clause
Duffer
Duffer

Reputation: 266

Select rows if it contains only one value

I am trying to find all the rows that have null in Col J to Col L

My output should look like below (Green is what I expect and others should be ignored):

enter image description here I tried below code but it didn't work:

-- Step 1 get all the data as required

select Col A,   Col B,  Col C,  Col D,  Col E,  Col F,  Col G,  Col H,  Col I,  Col J,  Col K,  Col L,
into tempDB
from MainDB

-- Step 2
select ColB, min(Col C) , min(Col D), min(Col E), min(Col F)
from tempDB 
where Col J = Col K and Col L = null
group by Col B

Upvotes: 0

Views: 1700

Answers (6)

Mahesh Waghmare
Mahesh Waghmare

Reputation: 744

SELECT 
  t.[Col A], 
  t.[Col B], 
  t.[Col C], 
  t.[Col D], 
  t.[Col E],
  t.[Col F],
  t.[Col G],
  t.[Col H],
  t.[Col J],
  t.[Col K],
  t.[Col L]
FROM tempDB t
INNER JOIN
(
  SELECT MIN([Col B]) AS [Col B]
    FROM tempDB
    GROUP BY 
        [Col B]
    HAVING
        MIN([Col J]) IS NULL
        AND MIN([Col K]) IS NULL
        AND MIN([Col L]) IS NULL
) d
  on t.[Col B] = d.[Col B]

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT ColB, 
MIN(ColC) , MIN(ColD), MIN(ColE), MIN(ColF)
FROM tempDB 
GROUP BY Col B
HEVING SUM(COALESCE (ColJ,0)) = 0
AND SUM(COALESCE (ColK,0)) = 0
AND SUM(COALESCE (ColL,0)) = 0

Upvotes: 2

forpas
forpas

Reputation: 164064

If you want colb where all the rows in colj, colk and coll contain nulls:

select ColB, min(ColJ), min(ColK), min(ColL)
from tempDB 
group by ColB
having coalesce(min(ColJ), min(ColK), min(ColL)) is null

Upvotes: 0

SuperAadi
SuperAadi

Reputation: 737

this should work.

SELECT *
FROM tempdb
where colj is null and colk is null and coll is null

You should not use something=null condition because null is not nothing but null is something that is not defined.

further read https://www.w3schools.com/sql/sql_null_values.asp

edit:- if you want rows based colb values

SELECT *
FROM tempdb
where colj is null and colk is null and coll is null and colb=4444

put '' around 4444 if the datatype is not numerical(char,varchar etc).

Upvotes: 1

KDM
KDM

Reputation: 144

You need to use IS NULL instead of = NULL. You cannot compare if null is equal to something, just if it is null. 

SELECT [ColB], MIN([Col C]), MIN([Col D]), MIN([Col E]), MIN([Col F]) 
FROM tempDB 
WHERE [Col J] IS NULL
  AND [Col K] IS NULL
  AND [Col L] IS NULL
GROUP BY [Col B]

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

I my understanding of your requirement is correct, you want rows where all values in ColJ, ColK, ColL are NULL value

select  ColB, min(ColC) , min(ColD), min(ColE), min(ColF)
from    tempDB 
group by ColB
having  min(ColJ)   is null
and     min(ColK)   is null
and     min(ColL)   is null

Upvotes: 2

Related Questions