Why isn't this left join query working

[john.melbourne]

I need a query that joins two tables on three common fields. It needs
to be a LEFT JOIN in that the I need ALL records from the first table
but only matching records from the second. This is not yielded by the
following SQL:

SELECT
tbAUM2.Geography,
tbAUM2.Product, tbAUM2.AssetClass,
tbPropSplit.Asset, [AUM2].[AUM_Value]*[tbPropSplit].[Perc] AS AUM
FROM tbAUM2 LEFT JOIN tbPropSplit
ON (tbAUM2.Geography = tbPropSplit.Geog)
AND (tbAUM2.Product = tbPropSplit.Product)
AND (tbAUM2.AssetClass = tbPropSplit.OrigAsset);

This returns only values of the INNER JOIN. If I drop one of the ON
clauses I get more rows in the result but obviously an incorrect one.

Why am I not getting all the records from tbAUM2?

 

[Michel Walsh]

That does not seem to be a cut and paste of your real query ( you use and
tblAUM2, and AUM2 ), so I suspect there is a where clause that implies
PropSplit and not providing the possibility for one of its value to be NULL
(AFTER the join has been done: that is because the WHERE clause is applied
AFTER the join, and if you use:

WHERE propSplit.perc >0

would destroy the job previously done by the outer join.


WHERE propSplit.perc > 0 OR propSplit.prec IS NULL


in Jet, or in MS SQL Server

tbAUM2 LEFT JOIN tbPropSplit
ON (tbAUM2.Geography = tbPropSplit.Geog)
AND (tbAUM2.Product = tbPropSplit.Product)
AND (tbAUM2.AssetClass = tbPropSplit.OrigAsset)
AND tbPropSplit.perc >0

(that last formulation DOES NOT WORK as intended, in Jet; Jet moves the last
condition, NOT implying two tables, to the WHERE clause).



Hoping it may help,
Vanderghast, Access MVP

 

[john.melbourne]

You're correct that I edited the SQL (sorry - I had a brainwave that
qry would be confusing) but there was no where clause.

SELECT qryAUM2.Geography, qryAUM2.Product, qryAUM2.AssetClass,
qryAUM_PropSplit3.Asset, [AUM_Value]*[Perc] AS AUM
FROM qryAUM2 LEFT JOIN qryAUM_PropSplit3 ON (qryAUM2.AssetClass =
qryAUM_PropSplit3.OrigAsset) AND (qryAUM2.Product =
qryAUM_PropSplit3.Product) AND (qryAUM2.Geography =
qryAUM_PropSplit3.Geog);

 

[john.melbourne]

You're correct that I edited the SQL (sorry - I had a brainwave that
qry would be confusing) but there was no where clause.

SELECT qryAUM2.Geography, qryAUM2.Product, qryAUM2.AssetClass,
qryAUM_PropSplit3.Asset, [AUM_Value]*[Perc] AS AUM
FROM qryAUM2 LEFT JOIN qryAUM_PropSplit3 ON (qryAUM2.AssetClass =
qryAUM_PropSplit3.OrigAsset) AND (qryAUM2.Product =
qryAUM_PropSplit3.Product) AND (qryAUM2.Geography =
qryAUM_PropSplit3.Geog);

I now spot the problem now but not the solution

If I put the same query into SAS but edit the FROM clause it works:

FROM qryAUM2 LEFT JOIN qryAUM_PropSplit3
ON (qryAUM2.AssetClass = qryAUM_PropSplit3.OrigAsset
AND qryAUM2.Product = qryAUM_PropSplit3.Product
AND qryAUM2.Geography = qryAUM_PropSplit3.Geog);

Note: inner brackets removed.

In fact I had previously solved the problem in Access using

FROM qryAUM2 LEFT JOIN qryAUM_PropSplit3
ON (qryAUM2.AssetClass & qryAUM2.Product & qryAUM2.Geography
= qryAUM_PropSplit3.OrigAsset & qryAUM_PropSplit3.Product &
qryAUM_PropSplit3.Geog);

....but this has obvious disadvantages.