Solved

query two tables

Posted on 2004-04-27
12
137 Views
Last Modified: 2013-12-24
Hi, if I have table T1 and T2 with the following data
T1
ID    Name  NameID   Phone
2      TOM      3         43-34-3
3      MIKE      4         423-23-35

T2
ID  Name       Address
3   LasVega   23 kjkl
4   Jiminy       32 klj;


when I perform a <cfquery> on t1 with ID 2
<cfquery name="test" datasource="whatever">
   select *
   from T1, T2
   where T1 = 2 and
             T1.NameID = T2.ID
</cfquery>

When I perform the cfoutput, how do I print the field Name that correspond to the T2? The problem occur is that T1 and T2 have fields Name.

Nick
0
Comment
Question by:SolverSurfer
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 125 total points
ID: 10934020
<cfquery name="test" datasource="whatever">
   select T1.name AS t1name, T2.name AS t2name
   from T1, T2
   where T1 = 2 and
             T1.NameID = T2.ID
</cfquery>

You'll have to use ALIASES for the columns as per above

If your only after the names you could also use UNION

<cfquery name="test" datasource="whatever">
   select T1.name
   from T1
UNION
   select T2.name
   from T2
</cfquery>
0
 

Author Comment

by:SolverSurfer
ID: 10934051
how would I do Aliases?
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 125 total points
ID: 10934053
You need to name your columns in the query like this :

<cfquery name="test" datasource="whatever">
   select T1.name as t1name, T2.name as T2name, specifyother columns here
   from T1, T2
   where T1 = 2 and
             T1.NameID = T2.ID
</cfquery>

Then you access by Test.T1name or Test.T2name

The downside is that you have to enumerate all the columns you are pulling...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10934057
Oh looks like TacoBell put up the same answer ;o)
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10934062
Aliases are done by

SELECT columnname AS Aliasname
FROM
...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10934120
ALiases are also a good method when you have really ugly or long columnnames such as

Pet's name

You can then do

SELECT [Pet's Name] as PetName

Then you don't have to worry that the ' may cause problems...
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 17

Expert Comment

by:Tacobell777
ID: 10934298
Hi SolverSurfer, di dyou actually have a look at the samples I provided?
0
 
LVL 11

Expert Comment

by:hart
ID: 10936270
just a small change in Tacobell777's solution

<cfquery name="test" datasource="whatever">
  select T1.name AS t1name, T2.name AS t2name
  from T1, T2
  where T1.ID = 2 and
            T1.NameID = T2.ID
</cfquery>

use t2name

PS: I think u forgot to put T1.ID = 2...

but Tacobell777's right, aliases are the key to ur solution

Regards
Hart
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 10937995
I would also consider using a join statment, not really answering your initial question, that has already been done, just adding my 2 cents

<cfquery name="test" datasource="whatever">
   SELECT T1.Name as FirstName, T2.Name as AltName
   FROM T1 LEFT JOIN T2 ON T1.NameID=T2.ID
   WHERE T1.ID = 2
</cfquery>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10940143
Jester,  the solutions we posted do use a join.  When you use a comma then SQL determines which type of join to use based on the where clause.  It is a different way of writing it.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 10940507
I just prefer to be explicit, my preference, not necessarily better
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10941092
I personally do too, but I just wanted SolverSurfer to realize that specifying the join wasn't something new since a join is already being done implicitly.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

13 Experts available now in Live!

Get 1:1 Help Now