Link to home
Start Free TrialLog in
Avatar of kensummers
kensummersFlag for Canada

asked on

How to reference same column twice from same table and then display in datagrid

I need to display data about cities/states in a datagrid based on a single quote. I need to display in a datagrid, Origin City, Destination City, Origin State and Origin State. The first two are from the Cities table and last two from the States table. My Quotes table has all four as foreign keys - OriginCityID, OriginStateID, DestCityID and DestStateID. My problem is how do I reference twice from same coumns in same table in my query and then display in datagrid. OriginCityID and DestCityID are basically from the same table, and same with the states.
Avatar of imitchie
imitchie
Flag of New Zealand image

select *, fromcity.CityName, tocity.CityName
from quotes
inner join cities fromcity on fromcity.CityID = quotes.OriginCityID
inner join cities tocity on tocity.CityID = quotes.DestCityID
select *, fromcity.CityName as OriginCityName, tocity.CityName as DestCityName
from quotes
inner join cities fromcity on fromcity.CityID = quotes.OriginCityID
inner join cities tocity on tocity.CityID = quotes.DestCityID

etc for the states
Avatar of kensummers

ASKER

Yes that worked perfectly. I want to learn though the how you did this. For example even though you are not referencing cities in the first line and just fromcity and tocity query still works.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>angelllllll everyone has standards. yours are not mine.
I do not disagree with that; and I did not suggest as a "standard"...

now:
select *, fromcity.CityName as OriginCityName, tocity.CityName as DestCityName ...

will NOT work for many ODBC/OLEDB clients running that query, as the * will result in duplicate column names.
NEVER (and I do mean NEVER!!) use * when you have multiple tables in the FROM clause, with or without join.

angel, you mean even specifically in the (ms) sql server zone which doesn't suffer from
>>will NOT work for many ODBC/OLEDB clients running that query, as the * will result in duplicate column names.
?
i fail to see the angst
@imitchie:
I am not sure what you mean/did not understand?
please enlighten us again why using "select *" is soooo bad when showing some code in a ms sql zone as a shortcut to having to type out some column names for a demonstration purpose. where's the fire? who's dying?
just a matter of bad habits. only when always doing the "remark" of not using it, it will keep in memory when writing sql code for the application(s) later.
I admit that, for a quick search/check in the tables, ad-hoc, I use "select * " myself almost every time.
but then, I also find myself often enough with a SELECT * in a query (and worst in a INSERT query) where it will be fatal.

so, in short: no-one will die from using it in a ad-hoc query to check/show things. but it will "fire" back when used in application code.
thanks for clearing that up for us then.
i'm well aware what it does, but i have to admit i have never had it occur to me where it would be fatal (physical death nor career)
appreciate your time and responses
>where it would be fatal
well, I had it once, because of such a query.
with the side effects of such a query, I had destroyed a full billing cycle of invoices of the company I was consultant for... some 100'000'000 Euro would not have been invoiced....

I was lucky, it was noticed in time, we had a good backup to restore, but it took a full day to get back on track...
Thanks for all ur responses...interesting discussion on the *.