Full Outer Join truncating secondary table results 1

[hanchilicious]

Hi,

I'm fairly new to SQL Server, and I'm using SQL Server 8.

I was wondering if someone would be able to take a look at this with me.

I have two tables TSRDB and TSR_LY. TSRDB is the driving table, containing customer and sales data for one fiscal quarter. TSR_LY does the same thing, but contains all four quarters from last year only.

If I run a straight query against TSRDB where class = 'IP' and country = 'FRANCE', I get a total of 18519. The same query on TSR_LY for the corresponding quarter reveals 8055. This is fine.

What I want to achieve is a single query illustrating 18519 for this Q1 this year, and 8055 for Q1 last year, even though some customers and products may not be in both tables.

I have the following:

select 
	sum(tsrdb.week1a) Q107, 
	sum(tsr_ly.week1a) Q106
from tsrdb
full OUTER JOIN TSR_LY 
ON  TSRDB.TruePPN = TSR_LY.TruePPN
AND TSRDB.TrueSalesDistrictID = TSR_LY.TrueSalesDistrictID
AND TSRDB.TrueSalesOrg = TSR_LY.TrueSalesOrg
AND TSRDB.TrueID = TSR_LY.TrueID
AND TSRDB.ClassID = TSR_LY.ClassID
AND TSRDB.HQID = TSR_LY.HQID
AND TSRDB.PPN = TSR_LY.PPN
AND TSRDB.SAPID = TSR_LY.SAPID
AND TSRDB.LY_FQ = TSR_LY.FQuarter
WHERE TSRDB.TrueSalesOrg = 'FRANCE'
-- either
AND tsrdb.fquarter = '2007.1'
AND tsrdb.classid = 'IP'
-- or
and (tsrdb.fquarter = '2007.1' or tsr_ly.fquarter = '2006.1')
and (tsrdb.classid = 'IP' or tsr_ly.classid = 'IP')
--

Although I get 18519 for Q107, I get 5915 for Q106, a shortage of some 2000. The join uses all dimension fields, so I can't tighten that up anymore, and I would have thought that using a full outer join would ensure that records in TSR_LY that are not in TSRDB would not diminish the Q106 total.

(The either/or criteria produce the same results, I'm just not sure if I drive the criteria from TSRDB alone, whether I will miss customers and products in TSR_LY who do not appear in TSRDB.)

Does anyone have any idea of what I should be looking out for here? I'm running a bit low on ideas.

Thanks

 

[fredericofonseca]

your where clause is basically changing the full outer join to a right outer, as it is not considering the records that are in TSR_LY but not in TSRDB.

WHERE (TSRDB.TrueSalesOrg = 'FRANCE' or isnull(TSRDB.TrueSalesOrg,'NULL' = 'NULL')
-- either
AND (tsrdb.fquarter = '2007.1' or isnull(tsrdb.fquarter,'NULL' = 'NULL)
AND (tsrdb.classid = 'IP' or isnull(tsrdb.classid,'NULL' = 'NULL')
-- or
and (tsrdb.fquarter = '2007.1' or tsr_ly.fquarter = '2006.1')
and (tsrdb.classid = 'IP' or tsr_ly.classid = 'IP')

Not complete, but should give you the idea.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Catadmin]

The problem may be that your join is too tight. Another way to find the missing 200 records is to start commenting out not only lines in your WHERE clause, but in the ON part of the join. Only comment out one line at a time, run the query again and see if you pick up the missing records. If it doesn't, un-comment the line and go to the next one.

BTW, I would NOT join on fields that, in the second table, could potentially be NULL. That will kill your record set right there.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[fredericofonseca]

My post should have said "left outer" and not "right outer".

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[hanchilicious]

Cripes. You're right, Frederico. I've been looking at this so long, I plain forgot to treat the 'FRANCE' clause the same way as the other ones.

In the end, I couldn't see the wood for the trees.

I put in an or TSR_LY equivalent for the country, and it was cool.

Thanks for your help. You too, Catadmin.