Left join with aliases

[kristinac]

I need to use a left join with a bunch of tables. The catch is I have to use aliases for a couple fields. When I try and use the working left join statement with the aliases, I get errors of all sorts.

SELECT DISTINCT Addresses.Address & ' ' & Streets.Name & ' ' & Streets.Type as StreetAddress, Addresses.*,
BStreet.Name & ' ' & BStreet.Type AS BeginningStreet,
EStreet.Name & ' ' & EStreet.Type as EndingStreet,
Segments.*, ESNs.*
FROM Streets BStreet, Streets EStreet, 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]

It's that BStreet and EStreet thing. I've tried joining those tables too but I continue to get errors. Is there a way to write this so that I can still find out the BStreets and EStreets but also LEFT JOIN?

 

[kristinac]

I tried this too...

SELECT DISTINCT Addresses.Address & ' ' & Streets.Name & ' ' & Streets.Type as StreetAddress, Addresses.*,
BStreet.Name & ' ' & BStreet.Type AS BeginningStreet,
EStreet.Name & ' ' & EStreet.Type as EndingStreet,
Segments.*, ESNs.*
FROM Streets AS BStreet INNER JOIN
(Streets AS EStreet INNER JOIN
(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])
ON EStreet.StreetId = Streets.Id)
ON BStreet.StreetId = EStreet.StreetId

 

[lespaul]

This probably isn't the exact syntax, but it will probably work if you set up your alias and joins like this:


SELECT DISTINCT Addresses.Address & ' ' & Streets.Name & ' ' & Streets.Type as StreetAddress, Addresses.*,
BStreet.Name & ' ' & BStreet.Type AS BeginningStreet,
EStreet.Name & ' ' & EStreet.Type as EndingStreet,
Segments.*, ESNs.*
FROM Streets
INNER JOIN (Streets AS BStreet on ([Streets].[ID] = [BStreet].[ID])
INNER JOIN (Streets AS EStreet on ([Streets].[ID] = [EStreet].[ID])



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]



Leslie

 

[RickSpr]

Why are you using BStreet and EStreet at all? In the first post, you're not joining them with Streets, so you'll get every possible combination of three streets in your output. I'm sure that's not what you want.

In the second post, you're joining them all right, but picking the same row three times from the same table! That can't be right, either.

From the structure of your query, it seems to me that the address range for a segment is always within one street, so I can't see why you need separate Beginning Street and Ending Street values. If you're just trying to get the same street name in two different columns, you can use
SELECT ...
Streets.Name & ' ' & Streets.Type As BeginningStreet,
Streets.Name & ' ' & Streets.Type As EndingStreet,
...
In other words, you can select the same column data into multiple output columns. Then you don't need BStreet and EStreet at all.

Is that what you're after? If not, how are you supposed to identify different Beginning and Ending Streets, when Segments has only one StreetID? Oh, and why are you using those LEFT JOINs?

(BTW, the term alias is properly used only for the alternate names you give to tables. The names you give to the output columns are just called result column names.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[kristinac]

A street segment has the street name, then the name of the street that begins the segment, and the name of the street that ends the segment. They are three different streets. I need all three displayed. The segments table has StreetId, BStreetId, and EStreetId. Plus an address range. I need the names of all three streets.

Also, I'm using a LEFT JOIN because not all addresses are within segments, but I still want them displayed. So I want all the addresses and only those segments that match addresses. Some addresses will have no matches.

I know it's confusing. But I do need the three separate streets, and I need addresses whether or not they fall in a segment.

 

[kristinac]

This statement:

SELECT DISTINCT Addresses.Address & ' ' & Streets.Name & ' ' & Streets.Type as StreetAddress, Addresses.*, BStreet.Name & ' ' & BStreet.Type AS BeginningStreet, EStreet.Name & ' ' & EStreet.Type as EndingStreet, Segments.*, ESNs.*
FROM Streets BStreet, Streets EStreet, Streets
INNER JOIN (Addresses
INNER JOIN (Segments
INNER 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
WHERE BStreet.Id = Segments.BStreetId
AND EStreet.Id = Segments.EStreetId
AND Streets.Id = Segments.StreetId
AND Segments.ESNId = ESNs.Id

...works fine until I change the joins to LEFT JOINs, which is what I need. This way I only get the addresses that are in segments. I have another statement that uses LEFT JOINs after Addresses, and it works well. It however does not have the BStreet and EStreet tables.

Is it the BStreet and EStreet tables that make the LEFT JOIN not work? The error I'm getting is 'Join expression not supported'. Can you not use Left Joins if you have alternate table names?

 

[RickSpr]

Kristinac,

The problem isn't anything to do with the table aliases. It's the combination of inner equijoins with the outer join using > and < operators. I admit I can't explain exactly why it's a problem, but I'm pretty sure that's what it is.

I would try a stacked query approach:
First, create a query 'SegmentAddresses' that enhances Segments by adding the street addresses and any ESN data available:
SELECT Segments.*,
BStreets.Name & ' ' & BStreets.Type AS BeginningStreet,
EStreets.Name & ' ' & EStreets.Type AS EndingStreet,
ESNs.*
FROM ((Segments INNER JOIN Streets AS BStreets
ON Segments.BStreetID = BStreets.ID)
INNER JOIN Streets As EStreets
ON Segments.EStreetID = EStreets.ID)
LEFT JOIN ESNs
ON Segments.ESNId = ESNs.ID

Then, select all the addresses and add SegmentAddresses data where available:
SELECT [Addresses].[Address] & ' ' & [Streets].[Name] & ' ' & [Streets].[Type] AS StreetAddress,
Addresses.*, SegmentAddresses.*
FROM (Streets INNER JOIN Addresses
ON [Streets].[ID]=[Addresses].[StreetID])
LEFT JOIN SegmentAddresses
ON [Addresses].[Address]>=[SegmentAddresses].[BAddress]
AND [Addresses].[Address]<=[SegmentAddresses].[EAddress]
AND [Addresses].[StreetID]=[SegmentAddresses].[StreetID];

I believe this gives you everything you need.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein