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

x
?
Solved

query two tables

Posted on 2004-04-27
12
Medium Priority
?
149 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
[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
  • 2
  • 2
  • +2
12 Comments
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 500 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 500 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
What You Need to Know when Searching for a Webhost Provider
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 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