Outer left join confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Outer left join confusion

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-06 : 12:16:05

Afternoon all

I am a little confused, and am hoping someone can offer a reasonable explanation. My understanding of a left join is that you can return the rows from the left hand table that has no matches in the right hand table. However, I have a query where I want to return all the LetterCodes and the number of letters that have been mailed (if any) for that lettercode for a specific date (CycleInstanceDate).

LetterCode MailDate        NumberOfLetters 
---------- --------------- --------------- 
028        06 Apr 2006     2384
030        06 Apr 2006     19
031        06 Apr 2006     133
041        06 Apr 2006     17
042        06 Apr 2006     1
043        NULL            0

I have three tables:
LetterCode
LetterCodeID
LetterCode

LettersMailed
LettersMailedID
LetterCodeID
MailDate
NumberMailed
CycleInstanceID

CycleInstance
CycleInstanceDate
CycleInstanceID

This query returns only the letterCOdes that have matching records in the LettersMailed table:


SELECT 	L.LetterCode
	, CONVERT(VARCHAR(15),MailDate,106) AS MailDate
	, SUM(NumberMailed)AS NumberOfLetters
FROM LetterCode L 
LEFT OUTER JOIN LettersMailed LM ON L.LetterCodeID = LM.LetterCodeID OR LM.LetterCodeID IS NULL
LEFT OUTER JOIN CycleInstance C ON C.CycleInstanceID = LM.CycleInstanceID
WHERE CycleInstanceDate = CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS SMALLDATETIME)
GROUP BY L.LetterCode, MailDate
ORDER BY L.lettercode

This query obtains the correct output:

SELECT 	  T1.LetterCode
	, ISNULL(CONVERT(VARCHAR(15),T2.MailDate,106),'N/A')AS [Mail Date]
	, ISNULL(T2.NumberOfLetters,'0')AS [Number Mailed]
FROM
(SELECT DISTINCT   L.LetterCode
		 , L.LetterCodeID
FROM LetterCode L)AS T1
LEFT JOIN
(SELECT   SUM(NumberMailed)AS NumberOfLetters
	, MailDate
	, LetterCodeID 
FROM LettersMailed LM
INNER JOIN  CycleInstance C ON C.CycleInstanceID = LM.CycleInstanceID
WHERE C.CycleInstanceDate = CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS SMALLDATETIME) 
GROUP BY LetterCodeID, MailDate)AS T2
ON T1.LetterCodeID = T2.LetterCodeID
ORDER BY T1.LetterCode

But should a left join not obtain the correct results in the first place? I would greatly appreciate an explanation on this. Thanks in advance.

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-06 : 12:21:12

By including a constraint on a column from a table on the right side of your join, you are effectively turning it into an inner join:

WHERE CycleInstanceDate = CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS SMALLDATETIME)

Change the 'WHERE' to an 'AND', thereby adding the condition to the join. This should get you the result you're looking for.

Mark

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 12:23:13

The problem is that a LEFT join will return all the records in the LEFT table and any that match in the RIGHT table. BUT, in your second join (between LettersMailed and CycleInstance) there are no records in the LEFT table (LettersMailed) for it to match on. If you were joining the third table back to the first one, then it would behave they way you were thinking, but since you are not, then there are no rows in the left table to display.

---------------------------
EmeraldCityDomains.com

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-06 : 12:23:30

Hmmmm, had a thought....is it because of the WHERE clause? If I wanted to return all the lettercodes, then those that have no letters mailed will also not have an associated CycleInstanceDate.

Right, just tried the query with the additional statement:

OR CycleInstanceDate IS NULL

and it works. I keep doing this, stressing over something for AGES, then post it here, and bingo. Please don't think I don't think about these things before I make a post, as I do. Its something to do with explaining the issue, and having to logically go through each step in order to explain it, and therefore, working it out myself.

Anyway, sorry for putting these pointless posts up!

Hearty head pats

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 12:25:53

msjdavidson has a good point about convertin an outer join into an inner join through the WHERE clause. I wrote an article about this and his recommendation called Additional Criteria in the JOIN Clause which may also be helpful.

---------------------------
EmeraldCityDomains.com

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-06 : 12:26:05

Crickey, you guys are quick in your responses. Thankyou both for taking the time to reply!

Its funny, but this has been bugging me for ages, so I left it for a while. Just goes to show...fresh eyes can see a lot clearer!

Thanks again!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-06 : 12:37:54

OK, I am not done yet. I tried out the method of using an AND in the join condition. However, I obtained only the matches using this query (because I am using an INNER JOIN):

SELECT 	L.LetterCode
	, CONVERT(VARCHAR(15),MailDate,106) AS MailDate
	, SUM(NumberMailed)AS NumberOfLetters
FROM LetterCode L 
LEFT OUTER JOIN LettersMailed LM ON L.LetterCodeID = LM.LetterCodeID 
INNER JOIN CycleInstance C ON C.CycleInstanceID = LM.CycleInstanceID
AND C.CycleInstanceDate = CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS SMALLDATETIME)
--OR CycleInstanceDate IS NULL
GROUP BY L.LetterCode, MailDate
ORDER BY L.lettercode

But if I used an OUTER JOIN, the result set was as follows:

LetterCode MailDate        NumberOfLetters 
---------- --------------- --------------- 
028        03 Feb 2005     2538
028        04 Feb 2005     2053
028        07 Feb 2005     3413
028        08 Feb 2005     2795
028        09 Feb 2005     1871
028        10 Feb 2005     2056
028        11 Feb 2005     1850
028        15 Feb 2005     2592
028        16 Feb 2005     1873
028        17 Feb 2005     1900
028        18 Feb 2005     1832
028        21 Feb 2005     3108
028        23 Feb 2005     4595
028        25 Feb 2005     1938
028        28 Feb 2005     3912
028        01 Mar 2005     3487
028        02 Mar 2005     2614
..................................ETC

So is there a way to achieve the required results using that method (in a join condition as opposed to a where clause)?

I have to go home now, but am intrigued to pick this up tomorrow. Thankyou!

Hearty head pats

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 13:20:27

Don't you actually want:

LEFT OUTER JOIN LettersMailed LM ON L.LetterCodeID = LM.LetterCodeID AND LM.MailDate = CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS SMALLDATETIME)
LEFT OUTER JOIN CycleInstance C ON C.CycleInstanceID = LM.CycleInstanceID
AND C.CycleInstanceDate = LM.MailDate

Not sure what the CycleInstanceDate is, so that last part may not be accurate, but the point is it sounds like you really want to filter on the LM.MailDate = (your preferred date).

---------------------------
EmeraldCityDomains.com

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-06 : 13:41:45

Hmmmm...not really. I'll try and explain...a cycleinstance is basically a billing run, and each run is identified by the date it occurred. I have to report on the number letters that have been generated for that billing run. The maildate is teh date on which the letter will be sent, therefore, not always the same day as the billing run.

However, that said, another report I do has to record the number of letters mailed that day, therefore, that method could work. But that said, I suppose the cycleinstance table would be irrelevant in that scenario as I would only use the maildate.

Anyway, its all food for thought. Thanks again!

Hearty head pats

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 17:09:09

I think you will then have to use a subquery like your solution above that works because the first LEFT JOIN is only limited by the values between the first two tables and the CycleInstanceDate is only restricting the join between the last two tables where what you are asking for is to essentially have the CycleInstanceDate limit the rows across all three table.

By the way, a side note on your date conversion. You are using 3 functions (2 casts and a floor) to strip the time portion off of the date. You could also do this with just two functions like:

SELECT CAST(CONVERT(varchar(8), GETDATE(), 112) AS smalldatetime)

---------------------------
EmeraldCityDomains.com

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-07 : 02:38:45

Ahhh, nice one. Thats a lot cleaner than my method. I always thought it was a little messy. Ta for that!

Hearty head pats

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-07 : 02:58:27

On the subject of stripping time portion,
Alternative method.

select dateadd(day, datediff(day, 0, getdate()), 0)

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-04-07 : 06:43:49

Here's another installment. I realised that actually, my approach was still not returning all the results. However, it was returning some of the lettercodes with no matches, but not all?!?!?!?

I have altered the query to use a subquery over a join, and used mwjdavidson advice of putting the clause into the join condition and it works. Here is the code:

SELECT 	L.LetterCode
	, CONVERT(VARCHAR(15),MailDate,106) AS MailDate
	, SUM(NumberMailed)AS NumberOfLetters
FROM LetterCode L 
LEFT OUTER JOIN LettersMailed LM ON L.LetterCodeID = LM.LetterCodeID 
AND (CycleInstanceID = (SELECT CycleInstanceID FROM CycleInstance 
			WHERE CycleInstanceDate = CONVERT(VARCHAR(15),GETDATE(),106)) 
OR CycleInstanceID IS NULL)
GROUP BY L.LetterCode, MailDate
ORDER BY L.lettercode

So now all the lettercodes are returned. What I don't understand, however, is why only some lettercodes were returned that had no matching values rather than either ONLY those that matched or ALL?

Does anyone have an explanation for this? Here is a sample of the correct output:

LetterCode MailDate        NumberOfLetters 
---------- --------------- --------------- 
028        07 Apr 2006     2108
030        07 Apr 2006     11
031        07 Apr 2006     127
032        NULL            NULL
033        NULL            NULL
041        07 Apr 2006     17
042        NULL            NULL
043        NULL            NULL
044        NULL            NULL
048        07 Apr 2006     2810
058        07 Apr 2006     618
060        07 Apr 2006     267
068        07 Apr 2006     36
069        07 Apr 2006     533
070        07 Apr 2006     60
071        NULL            NULL

Thanks again

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources