conditional for nulls and zeros

  • Thread starter Thread starter Han
  • Start date Start date
H

Han

The following query will exclude all records with LocationIDs equaling null
or zero.

SELECT DISTINCT [Users].[UserID], [Locations].[LocationName],
[Status].[StatusName]
FROM Users, Locations, Status
WHERE [Users].[StatusID]=[Status].[StatusID] AND [Users].[LocationID] =
[Locations].[LocationID];

I need a full recordset of UserIDs and StatusNames, even if LocationID
equals null or zero. Can a condition be added to accomplish this?

Thanks in advance,
Han
 
Your join is performed via a WHERE clause. Using an outer join, you should
be able to accomplish your need.

i.e. Select t1.f1, t1.f2, t2.f3, t2.f4 from t1 Left Join t2 on t1.id = t2.id

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Hi Steve,

Thanks for your response.

Unfortunately your suggestion did not return the desired results. It still
excludes records with LocationIDs that are null or zero.

The think the part that kills it is

Users.LocationID=Locations.LocationID

Since the null or zero value of Users.LocationID does not equal any of the
LocationIDs in Location, the record is ignored.

Any other suggestions/ideas?

Thanks,
Han

[MVP] S. Clark said:
Your join is performed via a WHERE clause. Using an outer join, you should
be able to accomplish your need.

i.e. Select t1.f1, t1.f2, t2.f3, t2.f4 from t1 Left Join t2 on t1.id = t2.id

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Han said:
The following query will exclude all records with LocationIDs equaling null
or zero.

SELECT DISTINCT [Users].[UserID], [Locations].[LocationName],
[Status].[StatusName]
FROM Users, Locations, Status
WHERE [Users].[StatusID]=[Status].[StatusID] AND [Users].[LocationID] =
[Locations].[LocationID];

I need a full recordset of UserIDs and StatusNames, even if LocationID
equals null or zero. Can a condition be added to accomplish this?

Thanks in advance,
Han
Click to expand...
Click to expand...
 
To reiterate and expand on Steve's suggestion. TRY the following.

SELECT DISTINCT [Users].[UserID], [Locations].[LocationName],
[Status].[StatusName]
FROM (Users LEFT JOIN Status
ON [Users].[StatusID]=[Status].[StatusID] Status)
LEFT JOIN Locations
 
John, that works great EXCEPT the "Status" at the end of the first JOIN was
invalid. Removing it returns the desired results.

Thank you,
Han

John Spencer (MVP) said:
To reiterate and expand on Steve's suggestion. TRY the following.

SELECT DISTINCT [Users].[UserID], [Locations].[LocationName],
[Status].[StatusName]
FROM (Users LEFT JOIN Status
ON [Users].[StatusID]=[Status].[StatusID] Status)
LEFT JOIN Locations
The following query will exclude all records with LocationIDs equaling null
or zero.

SELECT DISTINCT [Users].[UserID], [Locations].[LocationName],
[Status].[StatusName]
FROM Users, Locations, Status
WHERE [Users].[StatusID]=[Status].[StatusID] AND [Users].[LocationID] =
[Locations].[LocationID];

I need a full recordset of UserIDs and StatusNames, even if LocationID
equals null or zero. Can a condition be added to accomplish this?

Thanks in advance,
Han
Click to expand...
Click to expand...
 
Back
Top