July 30, 2010 at 7:44 am
SELECT
UA.UserID AS UserID,
CP.ProjectID,
CP.ProjectName,
ANS.ActualDueDate,
CP.TotalNumberOfIssues, -- we derive this on import and it sits on Projects
dbo.MainWorkItemIssueCounts(MWI.MainWorkItemID) as NumberRSIssues,
dbo.RestatedRiskLevel(MWI.MainWorkItemID) as RiskLevel, -- used a function here
EL.Description AS Engagement,
CP.Tier,
CP.CurrentPhase,
CP.Condition,
SCA.PossibleAnswer as RSCondition, -- from AnswerSessionDetail linked back to ScoreCardAnswer
COM.Comment, -- the comment for the condition answer in Scorecardanswer
LS.Description AS AssessmentStatus
FROM
MainWorkItem MWI
INNER JOIN
--- Note Inner Join does not matter because the join will only return matching JOINS
UserAssignment UA ON MWI.MainWorkItemID = UA.MainWorkItemID AND UA.AssignmentTypeLID = 1 -- 1 = Stability Manager
INNER JOIN
AnswerSession ANS ON MWI.MainWorkItemID = ANS.MainWorkItemID AND ANS.ScoreCardTemplateID = 27 -- 27 = RiskStablity ScoreCard
INNER JOIN
AnswerSessionDetail ASD ON ASD.AnswersessionID = ANS.AnswerSessionID AND ASD.ScoreCardQuestionID = 164 --RiskStability Condition
LEFT OUTER JOIN
-- OK here is my big question where do I need to be sure I have the correct order? ASD contains the PRIMARY KEY and SCA has the Foriegn KEY. So do I have the correct order? Where is the ORDER is it on the ON ASD.CommentID = SCA.ScoreCardAnserID because ASD is on the LEFT? Or is it because the JOIN comes after the Answer SessionDetail Join
ScoreCardAnswer SCA ON ASD.ScoreCardAnswerID = SCA.ScoreCardAnswerID
LEFT OUTER JOIN
Comment COM ON ASD.CommentID = COM.CommentID
INNER JOIN
Engagement ENG ON ENG.EngagementID = MWI.EngagementID AND ENG.BusinessGroupLID = 3 -- RiskStability Group
INNER JOIN
ClarityProject CP ON ENG.ProjectID = CP.ProjectID
INNER JOIN
LKPEngagementLevel EL ON MWI.EngagementLevelLID = EL.EngagementLevelLID
INNER JOIN
LKPStatus LS ON ANS.StatusLID = LS.StatusLID
WHERE
ANS.StatusLID NOT IN (2,6,12) -- 2 = Unable to complete, 6 = Complete, 12 = closed
AND
MWI.wfStatusLID IN (1,2) -- 1 = Setup, 2 = Tracking
AND
ENG.EngagedBy = 'John.Smith'
ORDER BY MWI.MainWorkItemID, DueDate DESC
July 30, 2010 at 2:54 pm
A LEFT JOIN will include data from the tables left of the join and whatever data that matches up from the tables on the right side of the join. A RIGHT JOIN does the opposite. An INNER JOIN will return only records where the defined values on both sides match up (thus, it's more limiting.)
INNER JOINS should be used wherever possible. Obviously there are times when you want to return whatever is there or null from certain tables and that's when you would use LEFT or RIGHT JOINS.
July 30, 2010 at 2:58 pm
bteraberry (7/30/2010)
A LEFT JOIN will include data from the tables left of the join and whatever data that matches up from the tables on the right side of the join. A RIGHT JOIN does the opposite. An INNER JOIN will return only records where the defined values on both sides match up (thus, it's more limiting.)INNER JOINS should be used wherever possible. Obviously there are times when you want to return whatever is there or null from certain tables and that's when you would use LEFT or RIGHT JOINS.
BT, thank you so much for the reply. I had that tatoo'd last week on my arm... LOL. However, going back to my oringinal post. What is the correct way for doing OUTER LEFT JOINs. AKA correct syntax.
July 30, 2010 at 3:15 pm
I don't want to pretend I have a complete knowledge of your table structure and your data and what you want to return, so I can't tell you exactly what you want to do.
Generally, I start with my tables that can be INNER JOINed to each other. Then, if I have other tables that may end up being null values in my query, I will use a LEFT JOIN to link them in. However, you can do the exact opposite and use a RIGHT JOIN instead. It's a matter of preference and organizational consistency.
Hopefully you know which tables may be joined in such a way as to properly result in null data in your query.
July 30, 2010 at 4:33 pm
I am having a hard time grasping the concept. Below I have just done an inner join (aka) returning all values from the AnswerSessionDetail table and AnswerSession if the tables have matching AnswerSessionID AND ScroreCardQuestionID = 164.
NEXT JOIN which is a LEFT OUTER JOIN of SCA ON ASD.ScoreCardAnswerID = SCA.ScoreCardAnswerID (which is the left table? Is it what is listed as the table immediatly after the LEFT OUTER JOIN Statement? OR determined by the ON ASD.ScoreCardAnswerID = SCA.ScoreCardAnswerID <-- Which one is on the left here? I just want to understand what determins the LEFT Table.
INNER JOIN
AnswerSessionDetail ASD ON ASD.AnswersessionID = ANS.AnswerSessionID AND ASD.ScoreCardQuestionID = 164 --RiskStability Condition
LEFT OUTER JOIN
ScoreCardAnswer SCA ON ASD.ScoreCardAnswerID = SCA.ScoreCardAnswerID
July 31, 2010 at 9:23 am
The table that will be considered the preserved table is the table that is logically to the left of the table you are joining in. The order of the join criteria (e.g. which table reference comes first/last) doesn't make a difference. You can use any order as long as their are no requirements to evaluate the expression in a particular order.
So, in a very simple query:
SELECT ...
FROM PreservedTable AS p
LEFT JOIN UnPreservedTable AS u ON u.ID = p.ID
Here, we are joining to the PreservedTable the UnPreservedTable using an outer join which will return all rows from the preserved table and only those rows that match from the unpreserved table.
Change the join from LEFT to RIGHT - and we would have to write the query as:
SELECT ...
FROM UnPreservedTable AS u
RIGHT JOIN PreservedTable AS p ON p.ID = u.ID
Now, if you have a complex join criteria - it could matter how they are evaluated. You would control that by using parantheses to force the evaluation order. Example:
SELECT ...
FROM PreservedTable AS p
LEFT JOIN UnPreservedTable AS u ON (u.SomeColumn = 'somevalue' AND u.ID = p.ID) OR u.OtherID = p.ID
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 10, 2010 at 5:22 am
This is worth a look.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 11, 2010 at 7:58 am
CELKO (8/11/2010)
...Using table valued functions is a bad programming technique that people who just don't understand declarative programming use to make the code feel like OO. This will mess up the optimizer, too.
Not necessarily. This is the SQL Server 2005 General Discussion forum, but it should be pointed out that single-statement table-valued functions are handled very well by SQL Server2008. Joining a single-statement table-valued function using APPLY will often result in the optimizer choosing a plan which is (almost) indistinguishable from JOINing a table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 12, 2010 at 6:38 am
CELKO (8/11/2010)
JOINs of all kinds are done in left to right order and follow the usual rules for parens. Use your parens to be sure the order is right. When you have this many tables, I would suggest that look at CTEs and VIEWs to reduce the complexity. A good heuristic is that you should never have more than five tables in a single query. It is a symptom of attribute splitting and other design flaws in the DDL.Your data elements keep changing names from table to table and don't always follow ISO-11179 rules.
Using table valued functions is a bad programming technique that people who just don't understand declarative programming use to make the code feel like OO. This will mess up the optimizer, too.
I would venture to say that while those may be good points for the designer and owner of the db, many (most?) of us are not in a position to completely redesign the db and change names/tables within the applications on which we are working. Good points to keep in mind while designing, not so helpful when you're just trying to get work done.
*Edit - ended a sentence with a preposition, and it was annoying me...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply