Return rows from table with a join and nulls

Question

Post reply

Return rows from table with a join and nulls

tshad

SSCertifiable

Points: 5905
More actions
March 27, 2019 at 11:10 pm

#2033871

I have a table where the status can be a value that is either in a reference table or is null.
I need to bring back the data that is based on one of three modes.
If mode is:
0: only records that have a status of null
1: only records that have a status of non-null joined with the status table
2: combination of both: nulls and non-nulls joined with a status table.
I have no problem with 0 or 1. But 2, comes back with duplicates for the nulls.
I can fix the issue using a union but I am trying to do it in one query. I also looked at using a LEFT JOIN but I that would mean I would get all the records back and then filter it in the WHERE clause. But there could be tens of thousands of records in the table.
Here are the results I am looking for:
But here are the results I am getting (the last two rows are duplicate rows).
DECLARE @test-2 TABLE ( EmpName varchar(50), EmpStatus VARCHAR(1), Job VARCHAR(10), YearMonth int )
DECLARE @status TABLE
(
JobStatus VARCHAR(10)
)
DECLARE @Mode INT
SELECT @Mode = 0
INSERT @status VALUES('T')
INSERT @status VALUES('L')
INSERT @test-2 Values('John', 'A', 'P', 201801)
INSERT @test-2 Values('John', 'L', 'P', 201802)
INSERT @test-2 Values('John', 'L', 'F', 201803)
INSERT @test-2 Values('John', 'A', 'P', 201804)
INSERT @test-2 Values('John', 'A', 'F', 201805)
INSERT @test-2 Values('Mary', 'A', 'F', 201801)
INSERT @test-2 Values('Mary', 'A', 'F', 201802)
INSERT @test-2 Values('Mary', 'T', 'F', 201803)
INSERT @test-2 Values('Mary', 'T', 'P', 201804)
INSERT @test-2 Values('Mary', 'A', 'F', 201805)
INSERT @test-2 Values('George', NULL, 'F', 201805)
INSERT @test-2 Values('Martin', NULL, 'F', 201805)
WHILE @Mode < 3
BEGIN
SELECT t.*
FROM @test-2 t
JOIN @status s
ON (@Mode = 0 AND t.EmpStatus IS NULL) OR
(@Mode = 1 AND t.EmpStatus = s.JobStatus) OR
(@Mode = 2 AND (t.EmpStatus IS NULL OR t.EmpStatus = s.JobStatus))
SELECT @Mode = @Mode + 1
END
Thanks,
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

tshad SSCertifiable Points: 5905 More actions · Answer 1

Actually the first set also gives duplicates (mode = 0). It is the nulls that are duplicates.

I can fix it by using DISTINCT but not sure if that is the best way.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

you have duplicates in 0, instead of a loop id create use a CTE using Row_number() to eliminate any duplicates

with cte as
(
 SELECT t.*,rank() over (partition by isnull(s.JobStatus,t.EmpStatus) order by isnull(s.JobStatus,t.EmpStatus) ) as rn, 1 as mode
 --row_number() over (partition by t.empname,job,yearmonth order by EmpStatus)as rn
 FROM #test t
 left JOIN Status s on s.JobStatus=t.EmpStatus
 where EmpStatus is null
 union all 
  SELECT t.*,rank() over (partition by isnull(s.JobStatus,t.EmpStatus) order by isnull(s.JobStatus,t.EmpStatus) ),2
 --row_number() over (partition by t.empname,job,yearmonth order by EmpStatus)as rn
 FROM #test t
 JOIN Status s on s.JobStatus=t.EmpStatus

 union  SELECT t.*,rank() over (partition by isnull(s.JobStatus,t.EmpStatus) order by isnull(s.JobStatus,t.EmpStatus) ),3
 --row_number() over (partition by t.empname,job,yearmonth order by EmpStatus)as rn
 FROM #test t
 left JOIN Status s on s.JobStatus=t.EmpStatus
)select * from cte
where rn=1
order by mode

***The first step is always the hardest *******

drew.allen SSC Guru Points: 76985 More actions · Answer 3

The problem here is that you are using an INNER JOIN when you should be using an OUTER JOIN and in trying to emulate an OUTER JOIN, you are creating a (partial) CROSS JOIN.

Also, I think that you would be better off with three separate queries rather than a loop with a single conditional query even if the query is much more complex than you've shown here.

DECLARE @test TABLE
(
 EmpName varchar(50),
 EmpStatus VARCHAR(1),
 Job VARCHAR(10),
 YearMonth int
)

DECLARE @Status TABLE
(
 JobStatus VARCHAR(10)
)

DECLARE @Mode INT
SELECT @Mode = 0

INSERT @Status VALUES('T')
INSERT @Status VALUES('L')

INSERT @test Values('John', 'A', 'P', 201801)
INSERT @test Values('John', 'L', 'P', 201802)
INSERT @test Values('John', 'L', 'F', 201803)
INSERT @test Values('John', 'A', 'P', 201804)
INSERT @test Values('John', 'A', 'F', 201805)
INSERT @test Values('Mary', 'A', 'F', 201801)
INSERT @test Values('Mary', 'A', 'F', 201802)
INSERT @test Values('Mary', 'T', 'F', 201803)
INSERT @test Values('Mary', 'T', 'P', 201804)
INSERT @test Values('Mary', 'A', 'F', 201805)
INSERT @test Values('George', NULL, 'F', 201805)
INSERT @test Values('Martin', NULL, 'F', 201805)

/*  Rewritten original code.  */
WHILE @Mode < 3
BEGIN

 SELECT t.* 
 FROM @test t
 LEFT OUTER JOIN @Status s
 ON t.EmpStatus = s.JobStatus
 WHERE  (@Mode = 0 AND t.EmpStatus IS NULL) OR
  (@Mode = 1 AND t.EmpStatus = s.JobStatus) OR
  (@Mode = 2 AND (t.EmpStatus IS NULL OR t.EmpStatus = s.JobStatus))

 SELECT @Mode = @Mode + 1

END

/*  As three separate queries.  */
SELECT t.*
FROM @test t
WHERE t.EmpStatus IS NULL

SELECT t.*
FROM @test t
INNER JOIN @Status s
ON t.EmpStatus = s.JobStatus

SELECT t.*
FROM @test t
LEFT OUTER JOIN @Status s
ON t.EmpStatus = s.JobStatus
WHERE t.EmpStatus IS NULL
OR s.JobStatus IS NOT NULL

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA