SQL JOIN two tables with null values

Question

I am having a hard time figuring this query out. I need records from issueMaster based on userId and visible, joined to issueActionDetails that are visible, but there may not be any issueActionDetails. I am only getting issues that have actions.

SELECT
b.AssignedTo, 
b.Visible,
c.ONEKEY_ID,
C.PHYS_NAME[Doctor],
B.IssueDescription[Issue to Address],
CASE WHEN A.ActionDescription IS NULL THEN 'To Be Assigned' ELSE A.ActionDescription END as [Action],
CASE WHEN A.AssignedTo IS NULL THEN 'To Be Assigned' ELSE A.AssignedTo END as [Assigned To],
CASE WHEN D.[Description] IS NULL THEN 'To Be Assigned' ELSE D.[Description] END As [Status]
FROM [dbo].[tbl_IssueMaster] B
LEFT JOIN  [dbo].[tbl_IssueActionDetails] A ON A.IssueID =B.IssueID
INNER JOIN [dbo].[tbl_DoctorsNew] C ON B.OnekeyID =C.ONEKEY_ID
INNER JOIN  [dbo].[Action_Status] D ON A.ActionStatus = D.ID 
WHERE B.AssignedTo = @UserId AND B.Visible =1 AND A.Visible =1
ORDER BY c.ONEKEY_ID,B.DisplayOrder ,A.DisplayOrder

Where is the problem with datetime and date ... I don't see what is your problem. — Valentin Clement
– Valentin Clement, Commented Aug 15, 2014 at 13:37
Your question body is not in the context what you said in question title. — Rahil Wazir
– Rahil Wazir, Commented Aug 15, 2014 at 13:40

spencer7593 · Accepted Answer · 2014-08-15 13:47:15Z

The "outerness" of the join to tbl_IssueActionDetails is negated by two things:

The inner join to Action_Status:

INNER JOIN  [dbo].[Action_Status] D ON A.ActionStatus = D.ID

And the predicate in the WHERE clause

AND A.Visible =1

For any rows returned where there isn't a matching row in tbl_IssueActionDetails, the values in the columns for that table will all be NULL. (The database is essentially creating a row from tbl_IssueActionDetails that consists of all NULLs, and "matching" that row to the row from the table on the left side.

Any predicate that excludes NULL values from columns in that table will exclude that row. If we specify to only return rows where "A.Visible=1", that will exclude any rows that have a NULL value in that column. Which is what I meant when I said the "outerness" of the LEFT JOIN operation was negated.

The fix is to move the predicate to the ON clause of the join to tbl_IssueActionDetail

and change that INNER JOIN to an outer join LEFT JOIN

Paul Maxwell · Accepted Answer · 2014-08-15 13:36:44Z

0

  LEFT JOIN [dbo].[tbl_IssueActionDetails] A
              ON A.IssueID = B.IssueID
                    AND A.Visible = 1

remove "AND A.Visible = 1" from the where clause, add it as a join condition instead

OR, alter the where clause to permit a NULL condition for the LEFT JOIN

WHERE B.AssignedTo = @UserId
      AND B.Visible = 1
      AND ( A.Visible = 1 OR A.Visible IS NULL)

If you always insist that A.Visible = 1 in the where clause you suppress the NULLs that a LEFT JOIN can provide. Be on guard for this whenever you use an outer join.

answered Aug 15, 2014 at 13:36

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

1 Comment

spencer7593 Over a year ago

There's another predicate that also negates the outerness of the LEFT JOIN: the predicate in the INNER JOIN on A.IssueID.

Collectives™ on Stack Overflow

SQL JOIN two tables with null values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related