SQL Syntax 1

[robman70]

I hope someone can help me, 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.

 

[TJRTech]

You need to join the City table twice, which may require that you alias it the second time (city2):

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

TR

 

[antiskeptic]

I think you might need to alias the "destination" table.

example:

SELECT master.*, city.*
FROM master
INNER JOIN city
ON master.origin = city.citykey
LEFT OUTER JOIN master as Destination
ON city.citykey = Destination.destination
WHERE rate = '150'

Okay..I think the syntax is right...I'm no guru...but I think this would do the trick and give you what you need. Hope it helps.

Sincerely,
AntiSkeptic

 

[SQLSister]

Not sure if I am correctly interpreting what you are asking for. This should get you the records where the rate is 150 and only one of the two fields has to link to the master table instead of both.

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

Now for some advice, never use select *. It is a poor programming practice, especially if you have a join or a union. The reason why is it returns more data than you need in a join and possibly more in any other query. This is inefficient use of server and network resources. Also in a union if someone changes the structure of one of the tables the code will usually break (Most unions aren't getting the same tables as this one is, this one would not break, but it is still a poor practice here as you have a join)

Next, Master is a very poor name for a table. Master is the name of a system database in SQL Server and as such for clarity you should avoid naming a table the same thing. Oddly it isn't a reserved word, so you avoid the problems of using one of those, but it is still a bad idea in my personal opinion.

Questions about posting. See faq183-874

 

[robman70]

Thanks for the quick replys, antiskeptic: i have no destination table. sqlsister: thanks for the advice, as i said this is all example, the real database that im using is not named master and im not using 'select *', the actually query is much more complex than this, i just needed a scaled down version for simplicity. TJRTech: i tried what you said, it seemed the most logical way and was my first instinct but i got a msg saying 'Tables or functions 'city' and 'city' have the same exposed names. Use correlation names to distinguish them.' the only problem was i dont know how to use correlation names. Thanks for the replies, any additional help appreciated

 

[robman70]

got it:

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'

thanks all

 

[antiskeptic]

I know you don't have a "Destination Table"

It is simply an alias for the Master table.

You have the "master" table is "posing" as another table so you can join the destination code to the city table. Maybe you'll see better what I mean if I use the logic I would normally use. I put my joins in the where clause...as below. Does this make more sense?

SELECT
master.*,
city.*

FROM
master,
Master AS Destination,
City

WHERE master.origin = city.citykey
and city.citykey = Destination.destination
and rate = '150'

 

[robman70]

yeah, i didnt really look at your post til after my last post, then i realized what you were doing, thanks

 

[TJRTech]

Right, like I said, you may have to alias the 2nd instance of the city table.

;-)

TR