Select a field twice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select a field twice

Author

Topic

alexandre_bastien
Starting Member

3 Posts

Posted - 2010-11-18 : 12:59:02

Hi guys,

I have two table that look like this:

Store:
ID   IDPhysCity   IDCorrCity
1    2            2
2    1            2
3    3            1
...

CityList:
ID   City
1    CityName1
2    CityName2
3    CityName3
...

I'm looking for a query that could display something like this:


IDStore   PhysCity   CorrCity
1         CityName2  CityName2
2         CityName1  CityName2
3         CityName3  CityName1

Most things that I tried gave me:
#1066 - Not unique table/alias

Any help would be appreciated.

Alexandre

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 13:03:47

i doubt if you're using MS SQL Server
If its MS SQL Server below works


SELECT s.ID AS IDStore,
cl1.City AS PhysCity,
cl2.City AS CorrCity
FROM Store s
INNER JOIN CityList cl1
ON cl1.ID = s.[IDPhysCity]
INNER JOIN CityList cl2
ON cl2.ID = s.[IDCorrCity]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alexandre_bastien
Starting Member

3 Posts

Posted - 2010-11-18 : 13:14:27

I'm testing it in phpMyAdmin on MySQL. The thing is that I only gave one CityList table, but I want to call it twice. Once for the IDPhysCity and once for the IDCorrCity.

alexandre_bastien
Starting Member

3 Posts

Posted - 2010-11-18 : 13:34:27

Ok, it worked perfectly! Thanks alot.

SELECT store.ID,
cl1.city AS PhysCity,
cl2.city AS CorrCity
FROM store
INNER JOIN citylist AS cl1
ON cl1.ID = store.IDCorrCity
INNER JOIN citylist AS cl2
ON cl2.ID = store.IDPhysCity

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-19 : 10:45:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources