ANSI Join on Multiple Tables
-
I have found several examples of changing code that used the *= to LEFT OUTER JOIN, but none with multiple tables.
Could someone please help me?
SELECT f.FolderId, FolderName,
SUM(CASE
WHEN Displayed IS null AND NOTEDATE IS NOT NULL Then 1
Else 0
END),
SortOrder
FROM OH_Folder f
OH_Mailbox m,
OH_Codes c
WHERE f.UserId = @UserId
AND f.FolderType = c.CodeId
AND f.FolderId *= m.FolderId
AND Displayed is null
AND getDate() >= NoteDate
How would I change the statement to use the JOIN and LEFT OUTER JOIN instead of *=
Thanks in advance!
-
It's the same as two tables. You put a join in between those tables.
select x, y
from a, b, c
where a.x = b.x
and a.y *= c.y
just list the tables
select x,y
from a
b
c
then add the joins you need
select x,y
from a
inner join b
left outer join c
then add the ON clauses:
select x,y
from a
inner join b
on a.x = b.x
left outer join c
a.y = c.y
-
SELECT f.FolderID, f.FolderName,
SUM(CASE WHEN Displayed IS NULL AND NoteDate IS NOT NULL
THEN 1
ELSE 0 END),
SortOrder
FROM OH_Folder f
INNER JOIN OH_Codes c ON f.FolderType = c.CodeID
LEFT OUTER JOIN OH_Maiilbox m ON f.FolderID = m.FolderID
WHERE f.UserID = @userid
AND Displayed IS NULL
AND GETDATE() >= NoteDate
Question : In the CASE statement, you put down when Displayed IS NULL and in the WHERE clause, you put down Displayed IS NULL, so in reality you don't need to put Displayed IS NULL in the CASE statement since you only select Dispalyed IS NULL.
You put down Notedate IS NOT NULL and in the WHERE Clause you put GETDATE() >= NoteDate, what happened when Notedate IS NULL? If NoteDate can be null, then you can get rid of the where clause and put the condition in the case statement.
SELECT f.FolderID, f.FolderName,
SUM(CASE WHEN NoteDate IS NOT NULL AND GETDATE() >= NoteDate
THEN 1
ELSE 0 END),
SortOrder
FROM OH_Folder f
INNER JOIN OH_Codes c ON f.FolderType = c.CodeID
LEFT OUTER JOIN OH_Maiilbox m ON f.FolderID = m.FolderID
WHERE f.UserID = @userid
-
SSChampion,
Thank you very much for breaking it down for me!
I really appreciate the help!
-
Thanks (Loner) and everyone!
I'm glad this forum is here!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply