CODEWITHSUNDEEP
sqlsql-server
user4187073
user4187073

Reputation:

Showing DISTINCT cases where NULL

I am trying to figure out all of the records that only have the value null in two columns. The problem I am running into is that

I have 565 ROWS with Columns including ID, Allegation1, allegation 2, date, concatenatedalleg12 so it shows up as 

ID    ALLEG1    ALLEG2    DATE    CONCATENATED
1     NULL      NULL      DATE1   NULL
1     NULL      A1        DATE1   A1
1     A2        NULL      DATE1   A2
2     NULL      NULL      DATE2   NULL
2     NULL      B1        DATE2   B2
3     NULL      NULL      DATE3   NULL
4     NULL      NULL      DATE4   NULL
5     NULL      NULL      DATE5   NULL
5     NULL      C1        DATE5   C1

I want to show only:

ID    ALLEG1    ALLEG2    DATE    CONCATENATED
3     NULL      NULL      DATE3   NULL
4     NULL      NULL      DATE4   NULL

How do I get only the distinct cases where there is only one row of nulls without including the 1 2 and 5 null rows that also have nonnull 2+ rows.

Upvotes: 0

Views: 137

Answers (4)

Dudi Konfino
Dudi Konfino

Reputation: 1136

Try use this query

select *
from xxx
where ((ALLEG1 is not  null) and (ALLEG2 is null) and (DATE is null) and (CONCATENATED is null))
or
((ALLEG1 is  null) and (ALLEG2 is not null) and (DATE is  null) and (CONCATENATED is  null))
or
((ALLEG1 is  null) and (ALLEG2 is  null) and (DATE is not null) and (CONCATENATED is  null))
or
((ALLEG1 is  null) and (ALLEG2 is  null) and (DATE is  null) and (CONCATENATED is not null))


having count(id)=1

Upvotes: 0

Stephen Bodine
Stephen Bodine

Reputation: 519

 SELECT 
    ID,
    MAX(ALLEG1) AS ALLEG1,
    MAX(ALLEG2) AS ALLEG2,
    MAX([DATE]) AS [DATE],
    MAX(CONCATENATED) AS CONCATENATED
 FROM 
    @TBL
 GROUP BY 
    ID
 HAVING
    SUM(CASE WHEN ALLEG1 IS NULL THEN 0 ELSE 1 END + CASE WHEN ALLEG2 IS NULL THEN 0 ELSE 1 END) = 0

Upvotes: 0

Barranka
Barranka

Reputation: 21047

Given that you only need to show the NULL place-holders, you could do something like this:

select ID
     , null as ALLEG1
     , null as ALLEG2
     , DATE
     , null as CONCATENATED
from yourTable
where ALLEG1 is null and ALLEG2 is null
group by ID, DATE;

of course, if you can simply ignore those columns, things become simpler:

select ID
     , DATE
from yourTable
where ALLEG1 is null and ALLEG2 is null
group by ID, DATE;

Then you can deal with those required columns in your front-end

Upvotes: 0

Hogan
Hogan

Reputation: 70513

WITH idswithonerow AS
(
   SELECT ID FROM
   (
      SELECT ID, 
             COUNT() OVER (PARTITION BY ID) AS CN
      FROM tablename   
   ) as tmp 
   WHERE CN = 1
)
  select ID, ALLEG1, ALLEG2, DATE, CONCATINATED
  FROM tablename
  WHERE ALLEG11 is null and ALLEG2 is null
  AND ID IN SELECT ID FROM idswithonerow

Upvotes: 3

Related Questions