Joining two tables with null values in the joined fields

[iamcaesar]

Hi,

I have two tables:

Table 1
field1 field 2
AAA BBB
CCC NULL

Table 2
field3 field 4
AAA BBB
CCC NULL


Where null is the null value, not the word null.

If I link these tables so that field1<->field3 and field2<->field4, and then write a query to get field1 and field2, the results are:

field1 field 2
AAA BBB

Basically, its not pulling any field where there is a null value as part of the link. Any idea how to fix this? I want the end results of this query to look like:

field1 field 2
AAA BBB
CCC NULL


I hope this isn't too confusing. Thanks in advance for any insights,

Caesar

 

[Norie]

I've just created Table1 and Table2 as per your example in Access.

I then ran this query.

SELECT Table1.field1, Table1.field2
FROM Table1 LEFT JOIN Table2 ON (Table1.field2 = Table2.field4) AND (Table1.field1 = Table2.field3);


It returned what you want.

The link between the tables should be One to Many, Table1 to Table2