SELECT Value even if NULL on LEFT JOIN

Question

I am trying to pull data out and chuck it into a Stimulsoft report. The problem I am having is that I need it to output to two columns. I also need every "manager" record to show even if the count assigned to said record is NULL.

This is what i have at the moment:

DECLARE @ManagerCount INT = (( SELECT Count(*) FROM AM WHERE dbo.AM.AMCurrent = 1 AND dbo.AM.OmitInReport = 0 ) + 1) / 2

DECLARE @tmp_AM1 TABLE (AMID INT, AMName NVARCHAR(100), ID INT)
INSERT INTO @tmp_AM1 SELECT AMID, AMName, row_number() over (order by AMID ) FROM AM
WHERE dbo.AM.AMCurrent = 1 AND dbo.AM.OmitInReport = 0  

SELECT * FROM (
    SELECT ta.id AS id1, ta.AMName AS ManagerName1, COUNT(*) AS ManagerCount1 FROM @tmp_AM1 tA  INNER JOIN Job J ON tA.AMID = j.AMID
    WHERE ta.ID BETWEEN 1 AND @ManagerCount AND j.jobStatusID != 5
    GROUP BY ta.ID, ta.AMName
) a
LEFT JOIN
(
    SELECT ta.id AS id2,ta.AMName AS ManagerName2, COUNT(*) AS ManagerCount2 FROM @tmp_AM1 tA  INNER JOIN Job J ON tA.AMID = j.AMID
    WHERE ta.ID > @ManagerCount AND j.jobStatusID != 5
    GROUP BY ta.AMName, ta.ID
) b ON a.id1 + @ManagerCount = b.id2

Which ends up returning something like:

There are 18 managers so 9 per column, but this code doesn't show them all since anything that doesn't have a count in the first left join, won't show, and therefore the same row in column 2 doesn't show.

Results of SELECT * FROM @tmp_AM1:

NB: You have an inner join (`@tmp_AM1 tA INNER JOIN Job J`) which may be removing them if not all managers have jobs assigned. Also are you sure all managers are in `@tmp_AM1`? Test this by running `select * from @tmp_AM1 ` — JohnLBevan, Apr 30 '18 at 06:14
Try each bit of the SQL by itself to ensure it gives what you'd expect, then join each bit up to see where it stops giving you the result you're after; then you'll know where the break is and can focus your efforts on that piece — JohnLBevan, Apr 30 '18 at 06:15
Thanks for the response John, I have tried each part sequentially. I can see every manager is there in tmp_AM1. It is the join that's the issue, but I would have thought if I was joining to the @tmp_1 table the count should always be 1? since there should be one row. — Owain Esau, Apr 30 '18 at 06:18

gotocartik · Answer 1 · 2018-04-30T06:23:18.597

DECLARE @tmp_AM1 TABLE (AMID INT, AMName NVARCHAR(100), ID INT)
INSERT INTO @tmp_AM1 SELECT AMID, AMName, row_number() over (order by AMID ) FROM AM
WHERE dbo.AM.AMCurrent = 1 AND dbo.AM.OmitInReport = 0  

SELECT * FROM (
    SELECT ta.id AS id1, ta.AMName AS ManagerName1, COUNT(*) AS ManagerCount1 FROM @tmp_AM1 tA  INNER JOIN Job J ON tA.AMID = j.AMID
    WHERE ta.ID BETWEEN 1 AND @ManagerCount AND j.jobStatusID != 5
    GROUP BY ta.ID, ta.AMName
) a
LEFT OUTER JOIN
(
    SELECT ta.id AS id2,ta.AMName AS ManagerName2, COUNT(*) AS ManagerCount2 FROM @tmp_AM1 tA  INNER JOIN Job J ON tA.AMID = j.AMID
    WHERE ta.ID > @ManagerCount AND j.jobStatusID != 5
    GROUP BY ta.AMName, ta.ID
) b ON a.id1 + @ManagerCount = b.id2 where ManagerName2 IS Null and ManagerCount2  IS NULL

just you want to use LEFT OUTER JOIN for select row even there is have any null values.,

Still didn't work, just excluded everything that was null. – Owain Esau Apr 30 '18 at 06:39 — Owain Esau, Apr 30 '18 at 06:39

score 0 · Answer 2 · answered Apr 30 '18 at 06:29

Since the two subqueries are pretty much identical, except the where-statement, I would consiter rewriting it into one single query. I'm not sure why you need the same columns outputed into different columns in the result, but something like this might work:

WITH cte AS (
    SELECT 
        ta.id AS id
        ,ta.AMName AS ManagerName
        ,COUNT(*) AS ManagerCount
        ,CASE WHEN ta.ID BETWEEN 1 AND @ManagerCount THEN 0 ELSE 1 END AS something
    FROM 
        @tmp_AM1 tA  
        INNER JOIN Job J ON tA.AMID = j.AMID
    WHERE
        j.jobStatusID != 5
    GROUP BY
        ta.ID
        ,ta.AMName
        ,CASE WHEN ta.ID BETWEEN 1 AND @ManagerCount THEN 0 ELSE 1 END
)
SELECT
    CASE WHEN something = 0 THEN cte.id ELSE null END AS id1
    ,CASE WHEN something = 0 THEN cte.ManagerName ELSE null END AS ManagerName1
    ,CASE WHEN something = 0 THEN cte.ManagerCount ELSE null END AS ManagerCount1
    ,CASE WHEN something = 1 THEN cte.id ELSE null END AS id2
    ,CASE WHEN something = 1 THEN cte.ManagerName ELSE null END AS ManagerName2
    ,CASE WHEN something = 1 THEN cte.ManagerCount ELSE null END AS ManagerCount2
FROM
    cte

Thanks, didn't work though it printed in column 2 only when column 1 was NULL — Owain Esau, Apr 30 '18 at 06:38
Try removing the case-statements for the first 3 columns then? I'm not really sure what you are trying to accomplish :) — TheQ, Apr 30 '18 at 06:40

Owain Esau · Accepted Answer · 2018-04-30T06:54:02.253

Probably not the best approach but i got the correct output using:

DECLARE @ManagerCount INT = (( SELECT Count(*) FROM AM WHERE dbo.AM.AMCurrent = 1 AND dbo.AM.OmitInReport = 0 ) + 1) / 2

DECLARE @tmp_AM1 TABLE (AMID INT, AMName NVARCHAR(100), ID INT)
INSERT INTO @tmp_AM1 SELECT AMID, AMName, row_number() over (order by AMID ) FROM AM
WHERE dbo.AM.AMCurrent = 1 AND dbo.AM.OmitInReport = 0 
ORDER By AMName 

SELECT ManagerName1, ManagerName2, ManagerCount1, ManagerCount2 FROM (
SELECT AMID, ta.id AS id1, ta.AMName AS ManagerName1 FROM @tmp_AM1 tA 
    WHERE (ta.ID BETWEEN 1 AND @ManagerCount)
) a
LEFT JOIN
(
    SELECT AMID, ISNULL(COUNT(*), 0) AS ManagerCount1 FROM Job j
    INNER JOIN tblJobOutcome jO ON j.JobOutcomeID = jo.JobOutcomeID AND jO.JobOutcomeID != 5
    GROUP BY AMID
) a1 ON a.AMID = a1.AMID
LEFT JOIN
(
    SELECT AMID, ta.id AS id2, ta.AMName AS ManagerName2 FROM @tmp_AM1 tA 
    WHERE (ta.ID > @ManagerCount)
) b ON a.id1 + @ManagerCount = b.id2
LEFT JOIN
(
    SELECT AMID, ISNULL(COUNT(*), 0) AS ManagerCount2 FROM Job j 
    INNER JOIN tblJobOutcome jO ON j.JobOutcomeID = jo.JobOutcomeID AND jO.JobOutcomeID != 5
    GROUP BY AMID
) b1 ON b.AMID = b1.AMID

Gives me the correct output in two columns.

gives me this:

How could count(*) ever be null? – paparazzo Apr 30 '18 at 08:28 — paparazzo, Apr 30 '18 at 08:28

SELECT Value even if NULL on LEFT JOIN

3 Answers3