• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

query two tables

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
SolverSurfer
Asked:
SolverSurfer
  • 6
  • 2
  • 2
  • +2
2 Solutions
 
Tacobell777Commented:
<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
 
SolverSurferAuthor Commented:
how would I do Aliases?
0
 
mrichmonCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mrichmonCommented:
Oh looks like TacoBell put up the same answer ;o)
0
 
mrichmonCommented:
Aliases are done by

SELECT columnname AS Aliasname
FROM
...
0
 
mrichmonCommented:
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
 
Tacobell777Commented:
Hi SolverSurfer, di dyou actually have a look at the samples I provided?
0
 
hartCommented:
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
 
James RodgersWeb Applications DeveloperCommented:
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
 
mrichmonCommented:
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
 
James RodgersWeb Applications DeveloperCommented:
I just prefer to be explicit, my preference, not necessarily better
0
 
mrichmonCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now