Help with finding unmatched data

[Guest]

I have 2 tables tbl_Date and tbl_ASX_Data.

Based on tbl_Date.ImportDate I want to find the dates for which a record
does not exist in tbl_ASX_Data where tbl_ASX_Data.ASXCode = "XAO". The field
ImportDate is the PK / FK respectively.

This is what I have so far but its not correct..............................

SELECT tbl_Date.ImportDate, tbl_ASX_Data.ASXCode
FROM tbl_Date LEFT JOIN tbl_ASX_Data ON tbl_Date.ImportDate =
tbl_ASX_Data.ImportDate
WHERE (((tbl_ASX_Data.ASXCode)="XAO"));

I know tbl_Date has 4292 records and tbl_ASX_Data has 1161 records for XAO,
so my query should give me the 3131 Null dates in question. The SQL above is
giving 1161.

Any help appreciated.

Bruce

 

[[MVP] S.Clark]

When you add a WHERE clause to an outer join, it will sometimes make it
behave as an inner join. So, you may need to create a query to perform the
WHERE, then link that query in the LEFT Join.

Also, to find records that are not there, you'll typically need to look for
an ID that is Null.

 

[Guest]

Thanks Clark.
I can handle the NULL bit and have left it out for now as there are 0
records based on the joins in my query. I will add this last..

I also know how to link from another query but would rather do this via a
subquery so that it is self contained as my DB already has heaps of query's
and I find it easier to manage if there are fewer.

Can you please help me how to do this by modifying my code so with a subquery?

Bruce

 

[[MVP] S.Clark]

I actually don't practice subqueries that much. Too hard to debug, IMHO.
So, my answer would probably be wrong, or it would take me too long to
figure out, or both.

Sorry,

 

[Marshall Barton]

Thanks Clark.
I can handle the NULL bit and have left it out for now as there are 0
records based on the joins in my query. I will add this last..

I also know how to link from another query but would rather do this via a
subquery so that it is self contained as my DB already has heaps of query's
and I find it easier to manage if there are fewer.

I don't understan the issue. Does't this do what you want?

WHERE tbl_ASX_Data.ASXCode="XAO"
OR ASX_Data.ImportDate Is Null