Table Alias -- Self-Join -- cfoutput problem 1

[corbitt]

I had to alias (self-join) a table:

<cfquery name="Query1" datasource="xxx">
select ap.cs_caseidd, bb.cs_caseidd, ap.c_number
from
case ap, case bb
where ...
</cfquery>

I'm trying to place the record(s) into a table:

<td>#Query1.ap.c_number#</td>
<td>#Query1.bb.cs_caseidd#</td>
...

However, I'm getting a error. The error states "ap.c_number is undefined in Query1"

What is the proper sytax for obtaining the record in the output in this particular situation?

Thank you for your help.

Jeremy

 

[ecobb]

You don't need the aliases in your output.
Try:
<td>#Query1.c_number#</td>
<td>#Query1.cs_caseidd#</td>



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[corbitt]

My ouput is actually going to look like this:

<td>#Query1.ap.cs_caseidd#</td>
<td>#Query1.bb.cs_caseidd#</td>

I'm sorry I wasn't accurate with my example!

 

[ecobb]

You will need to select the values AS something else, give them an alias.

<cfquery name="Query1" datasource="xxx">
select ap.cs_caseidd [red]AS CaseA[/red], bb.cs_caseidd [red]AS CaseB[/red], ap.c_number
from 
case ap, case bb
where ...
</cfquery>

<td>#Query1.CaseA#</td>
<td>#Query1.CaseB#</td>

Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[corbitt]

I'm going to try that this morning. I'll post the results.

Thanks!

 

[corbitt]

Ecobb,

That worked well! I just needed a little push from a programmer.

Happy Holidays,

Jeremy

 

[ecobb]

No problem, glad I could help!

Happy Holidays to you, too!



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[webmigit]

Good job.

Thank the good Lord for aliases.

Rudy should write a tutorial on aliases and call it A.K.A. SQL.

Haha.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

didn't realize aliases were worthy of a separate tutorial

but maybe so

this is not the first time i've seen a cf programmer attempt to use queryname.tablename.columnname syntax to distinguish between identically named columns in the result set



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[webmigit]

Its a fair assumption.

Sometimes there's only one way to learn.

As for the tutorial bit, I picked you because your our resident sql guru (resident is nicer than saying we have you locked up here ) and because it seemed like good humor to me.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.