Inner Join Problem 1

[MontgomeryPete]

Hello:

I am having difficulty with an inner join generating thousands of records on query execution.

We are matching addresses in order to load a geocentric address to optimize our routing. I have cleaned up the addresses to conform to the style provided by the software vendor. We have to match addresses using the street address, not a great way to do it, but necessary.

When I execute this code

SELECT Locations.LocationAddress, [Valid Addresses].Address, [Valid Addresses].[Disp Zone]
FROM Passengers, Locations INNER JOIN [Valid Addresses] ON Locations.LocationAddress = [Valid Addresses].Address;

I get the result mentioned above.

Any help would be appreciated.

Thanks, Pete

 

[r937]

each valid combination of location and valid address (determined by the Locations.LocationAddress = [Valid Addresses].Address join condition) is matched with every single passenger

either remove the Passengers table from the query...

SELECT Locations.LocationAddress
     , [Valid Addresses].Address
     , [Valid Addresses].[Disp Zone]
  FROM Locations 
INNER 
  JOIN [Valid Addresses] 
    ON [Valid Addresses].Address = Locations.LocationAddress

... or specify a valid join for it

SELECT Locations.LocationAddress
     , [Valid Addresses].Address
     , [Valid Addresses].[Disp Zone]
     , [blue]Passengers.????[/blue]
  FROM (
       Locations 
INNER 
  JOIN [Valid Addresses] 
    ON [Valid Addresses].Address = Locations.LocationAddress
       ) 
[blue]INNER
  JOIN Passengers
    ON Paggengers.???? = ????.????[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MontgomeryPete]

Thanks for the help. Sorry for the slow acknowledgment--family emergency.
This solved the problem.

Pete