Solved

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

Posted on 2007-11-19
13
232 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:kensummers
  • 6
  • 5
  • 2
13 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20317483
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
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20317487
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
0
 

Author Comment

by:kensummers
ID: 20317695
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.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 20317816
for queries in applicaitons, you should "never" use SELECT *.

now, a slightly better form would be:

select quotes.*, 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

finally, try to use (short) table aliases also:

select q.*, fc.CityName as OriginCityName, tc.CityName as DestCityName
from quotes q
inner join cities fc on fc.CityID = q.OriginCityID
inner join cities tc on tc.CityID = q.DestCityID

0
 
LVL 25

Accepted Solution

by:
imitchie earned 200 total points
ID: 20317935
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

basically,
inner join cities fromcity   -- means inner join with the CITIES table, but give it an alias (refer to as) fromcity
so you can keep referencing the table CITIES joined by OriginCity as FromCity (or you can give it any other alias)

so FromCity.CityName actually means the CityName column from the Cities table, the one joined to the OriginCity

angelllllll everyone has standards. yours are not mine. short names are despised by some as much as you despise "select *".  it has it's uses, especially when it's to illustrate a point
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20318100
>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.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Expert Comment

by:imitchie
ID: 20318152
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20318219
@imitchie:
I am not sure what you mean/did not understand?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20318261
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?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20318301
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.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20318313
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20318330
>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...
0
 

Author Comment

by:kensummers
ID: 20325799
Thanks for all ur responses...interesting discussion on the *.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now