Multiple Criteria for Outer Join

[Pisteuo]

I need to join a table (qryDCFForecasts) to a subquery (z). The subquery includes all possible criteria combinations for the following two fields: "fldPkg", "fldDCF"

Both (qryDCFForecasts) and the subquery (z) include both fields.

qryDCFForecasts does not include all possible combinations of fldPkg and fldDCF. However I still need to return records for all combinations. Therefore I am attempting to Outer Join qryDCFForecasts with subquery (z) - which lists all possible combinations. However I need to use both as criteria in the join.

In the below code, the WHERE statement below is filtering out Nulls from qryDCFForecasts. Do you have suggestions on how to include Null records from qryDCFForecasts?

SELECT z.fldPkg, z.fldDCF, qryDCFForecasts.fldValue
FROM
(SELECT zz.fldPkg, yz.fldDCF
FROM
(SELECT DISTINCT fldDCF FROM tblBudget) as yz,
(SELECT DISTINCT qryForecast.fldPkg FROM qryForecast) as zz
) AS z
LEFT OUTER JOIN
qryDCFForecasts ON z.fldDCF=qryDCFForecasts.fldDCF
WHERE z.fldPkg=qryDCFForecasts.fldPkg;

Thank you for the help.

 

[tehNellie]

It will do because of:

WHERE z.fldPkg=qryDCFForecasts.fldPkg;

If there's no record in qryDCFForecasts (ie fldpkg is NULL) it'll be filtered by this as NULL cannot be equal to a value from z.fldPkG even if z.flgpkg is also NULL.

WHERE z.fldPkg=qryDCFForecasts.fldPkg OR qryDCFForecasts.fldPkg IS NULL;

Should take care of that possibility.

 

[Pisteuo]

Thank you for the suggestions. qryDCFForecast Null records are still being filtered out on your suggestion and different flavors of the following IIF statement.

All attempts have returned the same records.

Btw, qryDCFForecasts.fldDCF is the problem field that causes Null criteria.

SELECT z.fldPkg, z.fldDCF, qryDCFForecasts.fldValue
FROM
(SELECT zz.fldPkg, yz.fldDCF
FROM
(SELECT DISTINCT fldDCF FROM tblBudget) as yz,
(SELECT DISTINCT qryForecast.fldPkg FROM qryForecast) as zz
) AS z
LEFT OUTER JOIN
qryDCFForecasts ON z.fldPkg=qryDCFForecasts.fldPkg
WHERE IIf(IsNull(qryDCFForecasts.fldDCF), "",z.fldDCF=qryDCFForecasts.fldDCF);

 

[Pisteuo]

I was able to use two sets of criteria with in the JOIN statement. For some reason I didn't think the obvious would have worked.

LEFT OUTER JOIN
qryDCFForecasts ON (z.fldDCF=qryDCFForecasts.fldDCF AND z.fldpkg = qryDCFForecasts.fldpkg);

All works fine and Null values are being returned.