30/05/2013
[MSSQL Join Problem]
So here I was, that fateful morning, got up very early [for once], sitting at my desk, faced with another SQL challenge that bothered me for 2 days when it dawned on me that the solution was right before mine eyes (always is :-) ). I was required to create a view [result set of a stored query β or map-and-reduce functions wikipedia.com] from 3 tables, 1 of which contained the foreign keys of the other two (hereafter referred to as middle table). But here's the catch, that same table had NULL values for either or both of the Foreign Keys. So Im thinking well I cant do Inner Joins since I'll only get one table to match at a time, maybe a case would work...but nah was too lazy to even think about that...then it hit me....Google It (always a life saver). I stumbled onto (link below) just to see my options. Figured i should learn them all and I found my friends Lefter and Right Outer Joins...Problem Solved
Id then left join Table 1 to the middle table the right join the other to that result set...thanks to Sub Querying...
Something like this:
SELECT Dum.*, dbo.[Level].*
FROM dbo.[Level]
RIGHT OUTER JOIN
(SELECT dbo.Activity_Log_Spelling.*dbo.Activity_Log_Spelling.FK_Level, dbo.Word.*
FROM
dbo.Activity_Log_Spelling
LEFT OUTER JOIN
db.Word ON dbo.Activity_Log_Spelling.FK_Word = dbo.Word.Unique_ID) AS Dum
ON
dbo.[Level].Unique_ID = Dum.FK_Level
And it worked like a charm...Moral is get up early and work it pays off!!! Learn your SQL!!!
I am new to SQL Server and want to learn about the JOIN options. What are all of the JOIN options in SQL Server? What is the significance of each of the options? I am a little confused on the differences and syntax, can you provide some examples and ex