SQL Syntax 1

[robman70]

I know this is more of an sql question than a vb/database question but i hope someone might still know the answer, the example im giving is a scaled down version of my problem. I have two tables, master and city, in the master table two of my columns are citykeys from the city table. My question is how can i get the two city names from the city column that match the citykey column..it sounds screwed up when i try to explain hopefully my example will help, if not please ask for clarification.

example code

select master.*, city.* from master inner join city on master.origin = city.citykey and master.destination = city.citykey where rate = '150'

example tables:

       master
------------------------------------
| origin | destination |   rate    |
------------------------------------
|  20    |     25      |    200    |
------------------------------------
|  33    |     21      |    150    |
------------------------------------
|  40    |     19      |    175    |
------------------------------------

        city
------------------------
| citykey |  cityname  |
------------------------
|   20    |  new york  |
------------------------
|   25    |  chicago   |
------------------------
|   21    |  houston   |
------------------------
|   33    | los angeles|
------------------------
|   40    |  phoenix   |
------------------------
|   19    |  boston    |
------------------------

with the example code given it will only match if master.origin and master.destination are the same, the above query in query analyzer produces no matches. Any help appreciated.

 

[zemp]

I think that you need to alias a second copy of the city table. Try something like this,

select master.*, city.*, city_1.cityname DestName from master inner join city on master.origin = city.citykey inner join city city_1 on master.destination = city_1.citykey where rate = '150'

zemp

 

[robman70]

damn zemp, you rule, thats exactly what i needed. thanks a million