Joins strangely affecting data...

[Naith]

Hi,

If I run the following, I get no data:

SELECT * FROM TSRDB
WHERE TSRDB.TrueSalesOrg = 'FRANCE'
AND TSRDB.Class = 'IBOOK'
AND TSRDB.TruePPN = 'PPM9623'
AND TSRDB.TrueAppleID = '53326'

However, if I join this table to some other tables to increase the columns returned, like this:

SELECT <some TSRDB fields>,<some History fields>
FROM TSRDB
INNER JOIN Reseller2.dbo.ProductHierarchyMaster ProductHierarchyMaster_Alias 
	ON (TSRDB.TruePPN=ProductHierarchyMaster_Alias.PPNID) 
	AND (TSRDB.ClassID=ProductHierarchyMaster_Alias.ClassID)) 
LEFT OUTER JOIN Reseller2.dbo.ProductHierarchyMaster ProductHierarchyMaster 
	ON TSRDB.TruePPN=ProductHierarchyMaster.STP_PPN) 
LEFT OUTER JOIN Reseller2.dbo.TSR_PrvQtr TSR_PrvQtr 
	ON (((((((TSRDB.TrueSalesOrg=TSR_PrvQtr.TrueSalesOrg) 
	AND (TSRDB.TruePPN=TSR_PrvQtr.TruePPN))
	AND (TSRDB.TrueAppleID=TSR_PrvQtr.TrueAppleID)) 
	AND (TSRDB.TrueSalesDistrictID=TSR_PrvQtr.TrueSalesDistrictID)) 
	AND (TSRDB.HQID=TSR_PrvQtr.HQID)) 
	AND (TSRDB.CustomerNumber=TSR_PrvQtr.CustomerNumber)) 
	AND (TSRDB.Product=TSR_PrvQtr.Product)) 
	AND (TSRDB.PPN=TSR_PrvQtr.PPN))
LEFT OUTER JOIN Reseller2.dbo.BillingAmtSplit2005 BillingAmtSplit2005 
	ON ((TSRDB.TruePPN=BillingAmtSplit2005.PPN) 
	AND (TSRDB.TrueAppleID=BillingAmtSplit2005.AppleHQID)) 
	AND (TSRDB.TrueSalesOrg=BillingAmtSplit2005.SalesOrg)) 
LEFT OUTER JOIN Reseller2.dbo.TSR_FC_History TSR_FC_History 
	ON ((TSRDB.TruePPN=TSR_FC_History.Product) 
	AND (TSRDB.TrueAppleID=TSR_FC_History.CustomerNumber)) 
	AND (TSRDB.TrueSalesOrg=TSR_FC_History.Country)                         
LEFT OUTER JOIN Reseller2.dbo.History History 
	ON (TSRDB.TruePPN=History.Product) 
	AND (TSRDB.TrueAppleID=History.CustomerNumber))
WHERE  TSRDB.TrueSalesOrg='FRANCE'
AND TSRDB.Class = 'IBOOK'
AND TSRDB.TruePPN = 'PPM9623'
AND TSRDB.TrueAppleID = '53326'

...then I get records. The where clause in the two statements is exactly the same.

Records exist in the History table matching the criteria, but not in TSRDB. Why would adding the joins suddenly make data appear when I do not expect it to be there?

 

[vbkris]

looks like your joins is the problem. how exactly did you write this join? did you use any tools???

Known is handfull, Unknown is worldfull

 

[Naith]

There's a front end which generates the joins at runtime, but the joins are pretty much as I would write them.

The problem was that the front end was aliasing an older archive of the table with the same name, so the results were actually from two separate datasources.

Naith

 

[vbkris]

so problem fixed huh???

Known is handfull, Unknown is worldfull