CODEWITHSUNDEEP
sqlsql-servert-sqlnullisnull
Hamza Bensaid
Hamza Bensaid

Reputation: 57

T-SQL: Conditional NULL removal

I need to select only the Room_IDs that have no instances where the Status is NULL.

For example here :

TABLE_A

  Room_Id   Status    Inspection_Date
  -----------------------------------
    1        NULL        5/15/2015
    2        occupied    5/21/2015
    2        NULL        1/19/2016
    1        occupied   12/16/2015
    4        NULL        3/25/2016
    3        vacant      8/27/2015
    1        vacant      4/17/2016
    3        vacant     12/12/2015
    3        vacant      3/22/2016
    4        vacant       2/2/2015
    4        vacant      3/24/2015

My result should look like this:

  Room_Id  Status  Inspection_Date
  -----------------------------------
    3       vacant      8/27/2015
    3       vacant     12/12/2015
    3       vacant      3/22/2016

Because Room_ID '3' has no instances where the Status is NULL

Upvotes: 0

Views: 74

Answers (4)

square_particle
square_particle

Reputation: 524

As alternative to Hashman, I just prefer to use not exists over not in for these types of queries.

Creating some test data

Note that I just kept the same date for everything since it's not imperative to the question.

create table #table_a (
    Room_Id int,
    Status varchar(32),
    Inspection_Date date);

insert #table_a (Room_Id, Status, Inspection_Date)
    values
        (1, null, getdate()),
        (2, 'occupied', getdate()),
        (2, null, getdate()),
        (1, 'occupied', getdate()),
        (4, null, getdate()),
        (3, 'vacant', getdate()),
        (1, 'vacant', getdate()),
        (3, 'vacant', getdate()),
        (3, 'vacant', getdate()),
        (4, 'vacant', getdate()),
        (4, 'vacant', getdate());

The query

select *
from #table_a t1
where not exists (
    select *
    from #table_a t2
    where t1.Room_Id = t2.Room_Id
        and Status is null);

The results

Room_Id     Status                           Inspection_Date
----------- -------------------------------- ---------------
3           vacant                           2016-06-17
3           vacant                           2016-06-17
3           vacant                           2016-06-17

Upvotes: 1

mschmidt
mschmidt

Reputation: 60

Try this out:

 SELECT *
 FROM Table1 
 WHERE Room_ID NOT IN 
 (
   SELECT DISTINCT Room_ID
   FROM Table1
   WHERE Status IS NULL
   )

The sub query returns a list of unique room id's that, at one time or another, had a NULL status. The outer query looks at that list, and says "Return * where the room_ID IS NOT one those in the subquery.

If you want to try it in SQL Fiddle, here is the Schema:

CREATE TABLE Table1
    (Room_ID int, Status varchar(8), Inspection_Date datetime)
;

INSERT INTO Table1
    (Room_ID, Status, Inspection_Date)
VALUES
    (1, NULL, '2015-05-15 00:00:00'),
    (2, 'occupied', '2015-05-21 00:00:00'),
    (2, NULL, '2016-01-19 00:00:00'),
    (1, 'occupied', '2015-12-16 00:00:00'),
    (4, NULL, '2016-03-25 00:00:00'),
    (4, 'vacant', '2015-08-27 00:00:00'),
    (1, 'vacant', '2016-04-17 00:00:00'),
    (3, 'vacant', '2015-12-12 00:00:00'),
    (3, 'vacant', '2016-03-22 00:00:00'),
    (4, 'vacant', '2015-02-02 00:00:00'),
    (4, 'vacant', '2015-03-24 00:00:00'),
    (2, NULL, '2015-05-22 00:00:00')
;

Upvotes: 1

Hashman
Hashman

Reputation: 141

Quick example of how to do it:

DECLARE @tTable TABLE(
Room_Id INT,
Status VARCHAR(20),
Inspection_Date DATETIME)

INSERT INTO @tTable VALUES 
 (1, NULL, '5/15/2015'),
 (1,NULL,        '5/15/2015'),
 (2,'occupied',  '5/21/2015'),
 (2,NULL,        '1/19/2016'),
 (1,'occupied',  '12/16/2015'),
 (4,NULL,        '3/25/2016'),
 (3,'vacant',    '8/27/2015'),
 (1,'vacant',    '4/17/2016'),
 (3,'vacant',    '12/12/2015'),
 (3,'vacant',    '3/22/2016'),
 (4,'vacant',    '2/2/2015'),
 (4,'vacant',    '3/24/2015')

 SELECT * FROM @tTable T1
  WHERE Room_Id NOT IN (SELECT Room_ID FROM @tTable WHERE Status IS NULL)

Gives :

Room_Id |   Status |    Inspection_Date         |
-------------------------------------------------
3       |   vacant |    2015-08-27 00:00:00.000
3       |   vacant |    2015-12-12 00:00:00.000
3       |   vacant |    2016-03-22 00:00:00.000

Upvotes: 1

kazem
kazem

Reputation: 3749

You can use CTE and NOT EXIST like below code

WITH    bt
      AS ( SELECT   RoomId ,
                    Status,
                    Inspection_Date
           FROM     dbo.Table_1
         )
SELECT  *
FROM    bt AS a
WHERE   NOT EXISTS ( SELECT 1
                     FROM   bt
                     WHERE  bt.RoomId = a.RoomId
                            AND bt.Status IS NULL );

Upvotes: 0

Related Questions