join 3 tables

[aniyahudi]

Hi, I have 3 simple tables as follows:
Table1:
OwnerID, Owner_LastName,


Table2:
TenantID, Tenant_LastName,


Table3
ID, Date, Amount, OwnerID, TenantID
I need query to display ALL the rows from Table3 and show columns of Owner_LastName and Tenant_Lastname. However, I want the rows in Table3, that do not have the value for TenantID to still appear, just with Tenant_LastName being left blank. How do I do that? Please provide an example.
Thanks
Aron

 

[brucey54]

Hi Aron, please find example attached, look at query 3, study the relationships, all the best.

 

[CazB]

the key is in your joins... you need right joins in order to show everything from Table3 and any related info from the other tables

SELECT Table3.ID, Table3.Date, Table3.Amount, Table1.Owner_LastName, Table2.Tenant_LastName
FROM Table1 RIGHT JOIN (Table2 RIGHT JOIN Table3 ON Table2.TenantID= Table3.TenantID) ON Table1.OwnerID= Table3.OwnerID

I would also recommend not using Date as a field name - change it to PaymentDate or something like that....

 

[pr2-eugin]

Cross posted : http://social.msdn.microsoft.com/Fo...42-b21eea84dfa9/join-3-tables?forum=accessdev

@aniyahudi - Please read : http://www.excelguru.ca/content.php?184