Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-19
13
Medium Priority
?
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 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 800 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 143

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
 
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 143

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 143

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 143

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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