Solved

query two tables

Posted on 2004-04-27
12
144 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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