Solved

query two tables

Posted on 2004-04-27
12
147 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reverse Proxy Server 6 101
retrieving files from old server once DNS has changed 10 74
Soundcloud.com 4 115
SSL on Apache 2... config file 1 33
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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