join on null fields..how does that work

[Ashank4vba]

Hi,

Im confused about the following query

SELECT 
RESTATEMENT_FUNDS_LIST.PARTNERSHIP_NUMBER, LRQC_REVIEW_METRICS_QRY.FISCALYEAR, LRQC_REVIEW_METRICS_QRY.RECEIVED, RESTATEMENT_FUNDS_LIST.PARTNERSHIP_FUND_NAME, TXDBOA_TBL_BALANCE_SHEET_SUMMARY.FISCAL_YEAR

FROM 
(RESTATEMENT_FUNDS_LIST LEFT JOIN LRQC_REVIEW_METRICS_QRY 

ON RESTATEMENT_FUNDS_LIST.PARTNERSHIP_NUMBER = LRQC_REVIEW_METRICS_QRY.PARTNERSHIP_ID) 

LEFT JOIN TXDBOA_TBL_BALANCE_SHEET_SUMMARY 

ON (LRQC_REVIEW_METRICS_QRY.PARTNERSHIP_NUMBER = TXDBOA_TBL_BALANCE_SHEET_SUMMARY.PARTNERSHIP_ID) AND (LRQC_REVIEW_METRICS_QRY.FISCALYEAR = TXDBOA_TBL_BALANCE_SHEET_SUMMARY.FISCAL_YEAR)

GROUP BY 
RESTATEMENT_FUNDS_LIST.PARTNERSHIP_NUMBER, LRQC_REVIEW_METRICS_QRY.FISCALYEAR, LRQC_REVIEW_METRICS_QRY.RECEIVED, RESTATEMENT_FUNDS_LIST.PARTNERSHIP_FUND_NAME, TXDBOA_TBL_BALANCE_SHEET_SUMMARY.PARTNERSHIP_ID, TXDBOA_TBL_BALANCE_SHEET_SUMMARY.FISCAL_YEAR

HAVING 

(((LRQC_REVIEW_METRICS_QRY.FISCALYEAR)=2004) AND ((LRQC_REVIEW_METRICS_QRY.RECEIVED)="Yes") AND ((TXDBOA_TBL_BALANCE_SHEET_SUMMARY.PARTNERSHIP_ID) Is Null) AND ((TXDBOA_TBL_BALANCE_SHEET_SUMMARY.FISCAL_YEAR) Is Null));

The query works, but if you notice the fields involved in the relation between the data sources LRQC_REVIEW_METRICS_QRY and TXDBOA_TBL_BALANCE_SHEET_SUMMARY are required to be Null!
How does the relationship work then?
I thought behavior with Nulls are undocumented.

But, I checked many times and get the same following number of records:
With null criteria for both the fields -> 18 records
Without null criteria for both the fields -> 356 records.
The number of records is consistent everytime.

Please clear my confusion!

Thanks a lot!

 

[r937]

oh, my goodness

i will never get over the shock of seeing non-aggregate conditions in the HAVING clause

the access query design view is responsible, and shot be taken out back and shot

and that GROUP BY looks mighty suspect, too

<sigh>

the reason the NULLs are tested for is precisely to take advantage of the way a LEFT OUTER JOIN works -- for rows from the left table that have no match in the right table, the columns from the right table are NULL in the result set

so if what you want is unmatched rows, that's what you test for

except it's normal to do this in the WHERE clause





r937.com | rudy.ca

 

[PCLewis]

It is a "LEFT JOIN" so all records from one table are present in the results (and then subjected to the criteria test) and ONLY the records from the second table where the key fields match are present.

This is the correct way to look for "unmatched records" in one table against another.

 

[Ashank4vba]

r937 & PCLewis - Thanks for your answers.

In my more-than-usual bonged state yesterday, I didnt see this. So I guess that 'HAVING' always evaluates the criteria after Joins and ... 'WHERE' does it before the Joins?

Another round about way that I thought of was to use
'SELECT * FROM <left table> WHERE <some field x> NOT IN (SELECT <some field x> FROM <right table>)' to choose records from 'left table' which are not present in 'right table' but I guess using left join, HAVING and Is Null would be faster.

 

[r937]

LEFT OUTER JOIN and WHERE ... IS NULL, not HAVING

r937.com | rudy.ca