November 11, 2009 at 11:10 am
I have a stored procedure that uses a .NET sessionID to return unique results. I added the sessionID column to every table that is used in this stored procedure. But now I'm wondering if my queries are overkill.
Here is one of the queries:
SELECT A.modeId, newmodeId, A.modeType, modeLinkID, modeTitle, A.questId, newquestId, B.sessionID, ISNULL(B.chocText, '') AS chocText,
ISNULL(B.patentResponse, '') AS patentResponse, B.chocId, newchocId, ISNULL(B.orderID, '') As orderID,
C.sessionID, D.sessionID, E.sessionID, F.sessionID
FROM modeList A
JOIN questchocs B ON A.questId = B.questId
LEFT JOIN modeLinks C ON C.chocID = B.chocID
LEFT JOIN XMLcaseMap D ON D.oldmodeID = A.modeId
LEFT JOIN XMLquestMap E ON E.oldquestID = A.questID
LEFT JOIN XMLchocMap F ON F.oldchocID = B.chocID
WHERE caseId = @caseID
AND A.modeType = 2
AND modeLinkID IS NOT NULL
AND C.sessionID = @sessionID
AND D.sessionID = @sessionID
AND E.sessionID = @sessionID
AND F.sessionID = @sessionID
ORDER BY A.questId
Do I actually need to check every table with .sessionID = @sessionID?
Thanks!
November 11, 2009 at 11:43 am
I dont think so! You're LEFT joining the tables and if there are no matches in 2nd,3rd, 4th table then you will have null for the sessionids there. Your where clause would filter these rows. I would check that only on first table!
Edited: Oh man, I just revisited your query and saw that you dont have sessionid on 1st table (A) at all. But my point will still apply, how do you want to approach the scenario where there are no matches on left join!
---------------------------------------------------------------------------------
November 11, 2009 at 11:50 am
I guess I should have stated that the sessionID is inserted into every table when the stored procedure is called.
So there would be no chance of a sessionID be NULL.
I just need to make sure that all tables are using data related to the same sessionID.
But I'm not sure if I still need to write the query the way I did...
Thanks!
November 12, 2009 at 5:05 am
Since you are using LEFT JOIN on C,D,E,F, we are under the impression that table A and B has some records which may or may not be there in C, D, E and F.
In that case , the output of the below two queries are different :
-- Query 1
SELECT A.modeId, newmodeId, A.modeType, modeLinkID, modeTitle, A.questId, newquestId, B.sessionID, ISNULL(B.chocText, '') AS chocText,
ISNULL(B.patentResponse, '') AS patentResponse, B.chocId, newchocId, ISNULL(B.orderID, '') As orderID,
C.sessionID, D.sessionID, E.sessionID, F.sessionID
FROM modeList A
JOIN questchocs B ON A.questId = B.questId
LEFT JOIN modeLinks C ON C.chocID = B.chocID
LEFT JOIN XMLcaseMap D ON D.oldmodeID = A.modeId
LEFT JOIN XMLquestMap E ON E.oldquestID = A.questID
LEFT JOIN XMLchocMap F ON F.oldchocID = B.chocID
WHERE caseId = @caseID
AND A.modeType = 2
AND modeLinkID IS NOT NULL
AND C.sessionID = @sessionID
AND D.sessionID = @sessionID
AND E.sessionID = @sessionID
AND F.sessionID = @sessionID
ORDER BY A.questId
-- Query 2
SELECT A.modeId, newmodeId, A.modeType, modeLinkID, modeTitle, A.questId, newquestId, B.sessionID, ISNULL(B.chocText, '') AS chocText,
ISNULL(B.patentResponse, '') AS patentResponse, B.chocId, newchocId, ISNULL(B.orderID, '') As orderID,
C.sessionID, D.sessionID, E.sessionID, F.sessionID
FROM modeList A
JOIN questchocs B ON A.questId = B.questId
LEFT JOIN modeLinks C ON C.chocID = B.chocID AND C.sessionID = @sessionID
LEFT JOIN XMLcaseMap D ON D.oldmodeID = A.modeId AND D.sessionID = @sessionID
LEFT JOIN XMLquestMap E ON E.oldquestID = A.questID AND E.sessionID = @sessionID
LEFT JOIN XMLchocMap F ON F.oldchocID = B.chocID AND F.sessionID = @sessionID
WHERE caseId = @caseID
AND A.modeType = 2
AND modeLinkID IS NOT NULL
ORDER BY A.questId
November 12, 2009 at 7:21 am
Oh ok,
Well each table will have a sessionID column that identifies which session the data belongs to.
A table row cannot exist without a sessionID entry.
So maybe I should just use JOIN without the LEFT?
Thanks!
November 12, 2009 at 7:54 am
Magy (11/12/2009)
Oh ok,Well each table will have a sessionID column that identifies which session the data belongs to.
A table row cannot exist without a sessionID entry.
So maybe I should just use JOIN without the LEFT?
Thanks!
Two different aspects here. One is 'join' and the other is your sessionID
entry. Your 'join' is joining all the tables (on other columns that you are
using). Now when you use 'LEFT' all the rows from your table A will be
returned even if there is no matching records on the other tables (on
the Right side of the join operator). The columns of the other tables in
your result set would contain null in this case.
Now you have to decide what kind of 'JOIN' you require. If you use 'Left'
the rows as discussed above will be filtered.
If you use 'JOIN' (Inner actually) you can join the tables on sessionID
also. Meaning your sessionID will come in ON clause.
Same when you use in 'WHERE' clause, the join will be done first and
then the records will be filtered after that based on your where
condition!
Please let us know if you need some more info. Thanks.
---------------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply