Help me with a JOIN Query with null values (1 Viewer)

[sailorguy]

Hi,

I am very new to Access still, and have a query to return values from multiple tables. In this case I want to return all users which have ANY of the following data in any of the following fields (DN, TN, Phantom, DID). This only works when the users has ALL the above fields. If any are null nothing shows up. Is there a way around this?

I am not too familiar with SQL code and pretty much rely on using the designer, but here is the code that it created if anyone can help!

SELECT tblDID.DID, tblDN.DN, tblPhoneLog.PhoneLogID, tblTN.TN, TblUsers.FirstName, TblUsers.LastName, tblPhantom.Phantom
FROM TblUsers INNER JOIN (tblTN INNER JOIN (tblPhantom INNER JOIN (tblDN INNER JOIN (tblDID INNER JOIN tblPhoneLog ON tblDID.DIDid = tblPhoneLog.DIDID) ON tblDN.DNid = tblPhoneLog.DNID) ON tblPhantom.Phantomid = tblPhoneLog.Phantomid) ON tblTN.TNid = tblPhoneLog.TNID) ON TblUsers.UserID = tblPhoneLog.UserID));

 

[llkhoutx]

An INNER JOIN requires values in both the left and right tables to return a result.

A LEFT JOIN is probably what you want. A LEFT JOIN returns all values in the left table and values in the right table that exist, all other rows return Null for the right tables.

Using an INNER JOIN on multiple tables can produce no results.

By analogy, a RIGHT JOIN return all values in the right tables anfd values in tghe left table that exist, all other rows return Null for the left tables.

I suggest that you locate a SQL hornbook. JOINs are elementary concepts.

 

[sailorguy]

O.k i did some reading but I'm not sure I can equate the examples to what I am doing. This is what I tried...but it doesn't work.

SELECT 
tblDID.DID, 
tblDN.DN, 
tblPhoneLog.PhoneLogID, 
tblTN.TN, 
TblUsers.FirstName, 
TblUsers.LastName, 
tblPhantom.Phantom

FROM TblUsers Left OUTER JOIN (tblTN, tblPhantom, tblDN, tblDID, tblPhoneLog)
ON (tblDID.DIDid = tblPhoneLog.DIDID)
ON (tblDN.DNid = tblPhoneLog.DNID)
ON (tblPhantom.Phantomid = tblPhoneLog.Phantomid) 
ON (tblTN.TNid = tblPhoneLog.TNID) 
ON (TblUsers.UserID = tblPhoneLog.UserID)

WHERE ((tblPhoneLog.PhoneLogID) Is Not Null));

 

[sailorguy]

O.k. I have not yet found an example of a LEFT JOIN to do what I am doing, joining multiple tables in this query. Is there anyone that can help me with the syntax on this? I'm not sure how it should be written (or if I indeed want a LEFT JOIN).

Tables are like so:

TblDN
DNid (PK)
DN

TblDID
DIDid (PK)
DID

TblTN
TNid (PK)
TN

TblPhantom
Phantomid (PK)
Phantom

TblUsers
Userid (PJ)
FirstName
LastName

tblPhoneLog
PhoneLogID (PK)
DNid
DIDid
TNid
Phantomid
Userid

 

[Brianwarnock]

You need to repeat the Left join and on

FROM TblUsers Left OUTER JOIN (tblTN, tblPhantom, tblDN

FROM TblUsers Left JOIN tblTN on ........... tblusers left join tblphantom On ..... Tblissues Left join etc

It is easier in the design grid.

Brian