left join query returning duplicates

[kristinac]

This is a little hard to explain so please bear with me.

I have 4 tables connected as follows:

Addresses
Streets
Segments
ESNs

Addresses.StreetId = Streets.Id
Streets.Id = Segments.StreetId
Segments.ESNId = ESNs.Id

I am running a query that I want to retrieve all addresses regardless of whether or not they fall in a segment. (A segment may have a beginning address of 100 and ending address of 200, and an address may fall within that range). So I used a left join. However, my problem now is this:

Say there are 4 segments for the same street with 4 different ESNs. When I run my query I get the same address 4 times. One for each ESN connected to a segment with the same streetId as the address.

I want each address once. The query should only pick up the ESN from the segment the corresponding address falls in. If I use WHERE Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress, then I only get the addresses that fall within segments. Not the ones that don't.

Here is my sql stmt:

SELECT DISTINCT [Streets].[City], [Streets].[Name], [Streets].[Type], [Streets].[Name]+' '+[Streets].[Type] AS Street, [Addresses].[Address],...ETC...[ESNs].[ESN]
FROM Addresses LEFT JOIN (Streets LEFT JOIN (Segments LEFT JOIN ESNs ON [Segments].[ESNId]=[ESNs].[Id]) ON [Streets].[Id]=[Segments].[StreetId]) ON [Addresses].[StreetId]=[Streets].[Id]
WHERE [Streets].[City]="RAEFORD" And [Addresses].[Address]
ORDER BY [Streets].[Name], [Streets].[Type], [Addresses].[Address]

Is there a way to write this statement so that it picks up all addresses once, but also retrieves the ESN if the address falls within a segment?

Any ideas would be appreciated. Thanks.

 

[swampBoogie]

Say there are 4 segments for the same street with 4 different ESNs.

In this case, which of the 4 ESN should be retrieved?

 

[kristinac]

Okay, if an address falls within a segment, that segment's ESN should be retrieved. There can be many segments for one street, each with different ESNs. The only one that matters though is the one for the segment that the address falls in. If the address doesn't fall within a segment, no ESN should be returned.

Am I trying to do the impossible?

 

[rac2]

An address has one street.
An address does not have a segment.
An address does not have an ESN.

A street may have none or many addresses.
A street may have none or many segments.
A street does not have an ESN.

A segment does not have an address;
although it must contain a range of addresses, so in a sense it has many addresses.
A segment has one street.
A segment has one ESN.

An ESN does not have an address.
An ESN does not have a street.
An ESN has one segment.

So

SELECT ...
FROM Addresses 
JOIN Streets ON 
     Addresses.StreetId = Streets.Id
LEFT JOIN Segments ON
     Streets.Id = Segments.StreetId
     AND Addresses.Address BETWEEN Segments.BAddress AND Segments.EAddress
LEFT JOIN ESNs ON
     Segments.ESNId = ESNs.Id

Each address is a row.

Since an address must be linked to a single street, we still have one row and we can't lose any rows.

A street usually has several segments but we only take the one that includes the address; and we left join in case there is no segment for this street address. And we still have one row.

A segment always has one ESN so we still have one row; but again we left join in case this street address is not in a segment hence is not associated with an ESN.

Maybe.

 

[kristinac]

Thank you... Yes I think I see what you're doing there. I'm trying the statement now however I am getting syntax errors in my from clause. I will keep trying to rearrange this one to make it work. I think you have the right idea.

 

[kristinac]

Okay here is what worked:

SELECT DISTINCT ...
FROM Streets INNER JOIN
(Addresses LEFT JOIN
(Segments LEFT JOIN ESNs ON [Segments].[ESNId]=[ESNs].[Id])
ON ([Addresses].[StreetId]=[Segments].[StreetId])
AND ([Addresses].[Address]>[Segments].[BAddress])
AND ([Addresses].[Address]<[Segments].[EAddress]))
ON [Streets].[Id]=[Addresses].[StreetId]

This seems to be working well. I'm still getting too many records though, because some addresses fall in the range of more than one segment (the data is a little messed up). Is there a way to say:

SELECT Addresses.Id
FROM Addresses
WHERE Addresses.Address
...falls between the beginning address and the ending address of more than one segment??

I need to find the messed up segments, but with 22000 records I'd much rather use a query to find them than go through them one by one. However I can't seem to get the syntax right.

Any ideas? Thanks...

 

[rac2]

Let's make a VIEW, in Access this is a saved query, and call it StreetSegments.

SELECT Streets.Id AS StreetID,
       Segments.Id AS SegmentID,
       Segments.BAddress,
       Segments.EAddress
FROM Streets
JOIN Segments ON
       Streets.Id = Segments.StreetId

This view pulls together the segments for each street for convenient reference.

Now we will join this view to itself.

SELECT a.SegmentID,
       a.BAddress AS "Target",
       b.BAddress,
       b.EAddress
FROM StreetSegments a
JOIN StreetSegments b ON
        a.StreetID = b.StreetID
        AND a.SegmentID = b.SegmentID

This ought to display one row for each street segment with the segment id, the beginning address twice, and the ending address. If the number of rows in this query is not equal to the number of rows in the StreetSegment view then something is wrong.

Now for the payoff; add a condition that identifies the overlapping segments.

SELECT a.SegmentID,
       a.BAddress AS "Target",
       b.BAddress,
       b.EAddress
FROM StreetSegments a
JOIN StreetSegments b ON
        a.StreetID = b.StreetID
        AND a.SegmentID = b.SegmentID
WHERE
   a.BAddress BETWEEN b.BAddress AND b.EAddress

Now that ought to yield the segments which overlap on the beginning address. If so, then a similar query will yield the ones which overlap on the ending address.

Let me know how it goes.

NOTES:
X BETWEEN Y AND Z is equivalent to X >= Y AND X <= Z.

There should not be any need to use DISTINCT in these queries. It makes me suspect that something is not working as I expected.

 

[kristinac]

Thank you! That helped a lot. I have to use DISTINCT though because I get even more duplicates if I don't. And I've looked at the data, that actually makes sense. But this is helping me identify the addresses covered by more than one segment.

 

[rac2]

Wait a minute that doesn't seem right.

Maybe join each row with a row that has an overlapping segment.

SELECT a.SegmentID,
                                a.BAddress AS "Target",
                                b.BAddress,
                                b.EAddress
                         FROM StreetSegments a
                         JOIN StreetSegments b ON
                                 a.StreetID = b.StreetID
                                 AND a.BAddress BETWEEN b.BAddress AND b.EAddress

So for a row with a given StreetID, JOIN any other rows with segments on the same street and with beginning and ending addresses that contain the beginning address of the given row.