CODEWITHSUNDEEP
sqlsql-server
Hasan Shouman
Hasan Shouman

Reputation: 2362

SQL get the IDs of the records that are null and no other records has values for the same ID?

I have the following data:

ID    From             To
----------------------------------
1      NULL            NULL
1      NULL            NULL
1      2019-01-01      2019-01-01
2      NULL            NULL
2      NULL            NULL
3      NULL            NULL
3      2019-02-01      2019-02-01

I need to get the IDs of the records that have NULL values in all of its records, in From and To fields.

For example, based on the above data I should get ID 2.

Upvotes: 1

Views: 2657

Answers (6)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this using NOT IN operator.

create table TestTable (id int, FromDt Date, ToDt Date)
Insert into TestTable Values
    (1, NULL, NULL), 
    (1, NULL, NULL), 
    (1, '2019-01-01', '2019-01-01'),
    (2, NULL, NULL),
    (2, NULL, NULL),
    (3, NULL, NULL),
    (1, '2019-02-01', '2019-02-01')

Select 
     distinct Id 
From TestTable where FromDt is null and ToDt is null
and id not in (Select t.Id from TestTable t 
         where t.fromDt is not null and t.toDt is not null)

Here is the live db<>fiddle demo.

It looks like as shown below.

enter image description here

Upvotes: 1

Erechtheus
Erechtheus

Reputation: 765

SELECT 
    D.Id 
FROM 
    dbo.YourDataTable D
WHERE Id IN -- ID must have null in both From and to fields
(
    SELECT 
        D.Id 
    FROM 
        dbo.YourDataTable D 
    WHERE 
       D.[From] IS NULL AND D.[To] IS NULL
    GROUP BY 
        D.Id 
) 
AND Id NOT IN -- Except if the id has From or To in any of its rows
(
    SELECT 
        D.Id 
    FROM 
        dbo.YourDataTable D 
    WHERE 
       D.[From] IS NOT NULL OR D.[To] IS NOT NULL
    GROUP BY 
        D.Id 
) 
GROUP BY Id

You can add a calculated column named "noData" or similar to your table:

ALTER TABLE dbo.YourDataTable ADD hasData AS (IIF([From] IS NULL AND [To] IS NULL,0,1))

, add an index to it and use WHERE D.noData = 1 clause instead if you have many rows for better performance.

Upvotes: 0

Prajakta Kale
Prajakta Kale

Reputation: 391

Please refer this for solution. And you should not use reserved keywords like From and to as a column names.

select id from sample  group by id having max(From_) is null and max(To_) is null;

I replaced from and to by from_ and To_ .

Upvotes: 0

nfgl
nfgl

Reputation: 3202

Another suggestion using except, all ids except those where from or to is not null

select id from t
except
select id from t where from is not null or to is not null

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

Gordon's method is far more concise, and I'd wager more efficient, though I haven't tested that. But just as an alternative, another way to find list elements that do, or do not, occur in conjunction with some other element is to use a WHERE EXISTS clause. Or, in the negative case like this one, a WHERE NOT EXISTS clause.

select distinct
    id
from 
    mytable as t1
where 
    not exists (select 1
                from mytable as t2
                where [to] is not null
                and t2.id = t1.id)
    and
    not exists (select 1
                from mytable as t2
                where [from] is not null
                and t2.id = t1.id);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use aggregation:

select id
from t
group by id
having max(from) is null and max(to) is null;

Note that from and to are SQL keywords, so they are very bad for column names. I didn't escape the values, assuming that you just simplified the column names for your question.

Upvotes: 4

Related Questions