Need a Join between tables to include NULLs

[NeedsHelp101]

Hi,

I have three tables in a query - tblFullName, tblHalf, tblHalf_1 . tblHalf and tblHalf_1 are identical. In tblFullName, there are columns: FullName, HalfName1, HalfName2. I'm trying to link HalfName1 to its corresponding column in tblHalf, and HalfName2 to its corresponding column in tblHalf_1. The joins work perfectly when there are no nulls in the record.

However, when, for example, only FullName and Half1 are filled in (FullName and Half1 will never be null though), the query completely ignores those entries, and only pulls up records where all three columns have entries.

Essentially, I need to pull all the records in FullName, and somehow make sure that when Half2 is Null, Half1 still shows up (and leaves a blank entry in Half2).
Help?
Thanks!

 

[PHV]

Use a LEFT join with tblHalf_1 instead of an INNER join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeedsHelp101]

I've tried that, but it doesn't work because I'd be trying to join a Null in Half2 to nothing at all in tblHalf_1. I'm not even sure if what I want is possible..

 

[PHV]

Could you please post your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeedsHelp101]

Here's my SQL - Master1 and Master2 are the "Half Names" and ISCI is the Full Name. As I said before, sometimes Master2 in the ISCI table will be null, but Master1 won't. I need Master1 to show up regardless though, and right now, it only shows if Master2 is not null.

SELECT tblISCI.Master1, tblISCI.Master2, tblISCI.ISCI, IIf([tblISCI].[Master2]=Null,[tblMasterNames].[SpotName],[tblMasterNames.SpotName] & " / " & [tblMasterNames_1.SpotName]) AS Name

FROM (tblMasterNames AS tblMasterNames_1 INNER JOIN tblISCI ON (tblMasterNames_1.MasterNumber = tblISCI.Master1)) INNER JOIN tblMasterNames ON  (tblISCI.Master2 = tblMasterNames.MasterNumber);

Also, today, when I try to experiment again with different types of joins, the program tells me I have "ambiguous outer joins" so the query can not be executed.
But thanks in advance for the help!

 

[PHV]

you may try something like this:
SELECT I.Master1, I.Master2, I.ISCI, M1.SpotName & (' / ' + M2.SpotName) AS Name
FROM (tblISCI AS I
INNER JOIN tblMasterNames AS M1 ON I.Master1 = M1.MasterNumber)
LEFT JOIN tblMasterNames AS M2 ON I.Master2 = M2.MasterNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NeedsHelp101]

Thank you so much, that worked perfectly!
I also have to concatenate lengths (in seconds), but I get #Error when I try this -

 [tblMasterNames.Length] & (" / "+[tblMasterNames_1.Length]) AS Duration

Also, though in the table these lengths are in short time 0:30, 0:15, etc, they show up as 12:15:00 AM in the query for some reason. Any ideas?