CODEWITHSUNDEEP
sqlms-accessjoin
Shane LeBlanc
Shane LeBlanc

Reputation: 2643

Left Outer Join To Get All Rows?

I'm working with an access database and I have some tables set up like this.

Table1(Table1Id, Field1, Field2, ...)
Table2(Table2Id, Table1Id, AuditDate, Field1, Field2, ...)

I am trying to select every record in Table1, and those in Table2 with the latest AuditDate. If there is no entry in Table2 to tie back into Table1, I just want to populate a row of what is in Table1 and a bunch of empty fields where the data from Table2 would come from. Here is the query I have so far.

SELECT Table1.TableId, 
       Table1.Field1, 
       Table1.Field2, 
       Table2.Field1, 
       Table2.Field2 
FROM Table1 
LEFT OUTER JOIN Table2 ON Table1.TableId = Table2.Table1Id 
WHERE Table2.AuditDate = (SELECT MAX(AuditDate) FROM Table2 WHERE Table1.TableId = Table2.Table1Id)

The problem with this is that it only returns two records. There are only two entries in Table2 but I want it to return a row of data for every record in Table1 as well instead of just the ones that find a match in Table2.

Upvotes: 0

Views: 205

Answers (2)

PinnyM
PinnyM

Reputation: 35531

The problem is this line:

WHERE Table2.AuditDate = ...

In cases where no Table2 entry exists, AuditDate will be NULL. So you need to test for that:

WHERE Table2.Table2Id IS NULL OR Table2.AuditDate = ...

Upvotes: 3

Theolodis
Theolodis

Reputation: 5092

Guess that if you remove the outer you would get every entry from table 1.

Edit: but if you have more entries in Table 2 than in Table 1 it will still be a problem. Maybe try a Full JOIN?

Upvotes: 0

Related Questions