Query does not return rows where a related ID field = 0 1

[papadilbert]

I have a table that contains rows. (duh)
The rows have String fields and a few Long fields.
The Long fields reference the IDs of related table rows.
So far .. nothing unusual.

I created a query using the design tool. I designated that I want the query to return all of the rows matching one of the String fields and return not ID's of the related tables rows, but a field in the related row.
Still... nothing unusual. Just standard stuff. The query tool constructed the sql with the correct SELECT statement, JOIN statements, WHERE statement, etc.

When I execute the query, I get all of the matching rows returned only when there is an ID in the referenced ID fields. If, in a row, a referenced ID field = 0, that row does not get returned on the SELECT.

For example:

Table_A
StreetAddress as String
StateID as Long


Table_B
ID as Long
StateName as String


Table_A Rows:
123 ABC Street , 1
123 ABC Street , 0 'This one does not get returned on SELECT
123 ABC Street , 2

Table_B Rows:
1 , ALABAMA
2 , ALASKA
3 , ARIZONA
4 , ARKANSAS

If I:

SELECT Table_A.StreetAddress; Table_B.StateName WHERE Table_A.StreetAddress = "123 ABC Street"

I only get 2 rows returned. The row with the 0 as the StateID does not get returned.

I need all three rows returned even if there is no StateID. Do I need to add something to my sql to specify that a row with no StateID reference is OK?

 

[dhookom]

Why not share the entire SQL statement. I expect someone could fix it for you with a LEFT or RIGHT JOIN.

Try double-click the join line to open its property dialog. Select the option that returns all of the records from Table_A.

Duane
Hook'D on Access
MS Access MVP

 

[papadilbert]

OK. So here is the real SQL. I've also included a link to an image of the query design tool.

Anytime the ID reference to a related table is 0, that row is not returned even if the WHERE clause is satisfied. LEFT/RIGHT JOIN was mentioned. I'll read up on that.


SELECT ServersIPDups.[DB Server IP], ServersIPDups.[DB Server Barcode], ServersIPDups.[Virtual Server], ServersIPDups.Description, Environments.Acronym, HardwareTypes.HardwareType, Locations.Acronym, OSTypes.OSType, ServerTypes.ServerType, DBTypes.DBType, RespOwners.RespOwnerAcronym
FROM RespOwners INNER JOIN (DBTypes INNER JOIN (ServerTypes INNER JOIN (OSTypes INNER JOIN (Locations INNER JOIN (HardwareTypes INNER JOIN (Environments INNER JOIN ServersIPDups ON Environments.ID = ServersIPDups.EnvironmentID) ON HardwareTypes.ID = ServersIPDups.HardwareTypeID) ON Locations.ID = ServersIPDups.LocationID) ON OSTypes.ID = ServersIPDups.OSTypeID) ON ServerTypes.ID = ServersIPDups.ServerTypeID) ON DBTypes.ID = ServersIPDups.DBTypeID) ON RespOwners.ID = ServersIPDups.RespOwnerID
WHERE (((ServersIPDups.[DB Server IP])=[IPAddress]));

 

[dhookom]

Do as I suggested to set the join option(s).
BTW: I don't think I would have a default value of 0 on the "ID" fields.

Duane
Hook'D on Access
MS Access MVP

 

[papadilbert]

The hint about LEFT/RIGHT JOIN solved my problem.

Thanks dhookom!