Accessing duplicate field names from different tables

[cakesy]

Hi,

I am joining two tables, and the two tables have the same field names, but
they represent different data. eg.

table 1 - People
name - there name
title - there title (mr, dr,...)
email - email address
id - user id

table 2 - Reports
title - title of the report
date - submitted date
id - user id

When I join these two table by there user id, I want to access both the title
variables from the webpage (at the moment I am doing a select * from People,
Reports where id=id; )

I have tried searthing for this information, but can't find anything.

I also tried <cfoutput> #people.title# - and it didn't work

cakesy

[vkunirs]

hi

when you are writing the query give the alias name for the table then it will works.

[cakesy]

Thanks for your reply.

I tried doing

<CFOUTPUT> #p.title#</CFOUTPUT>

but that would no work. it comes up with an error page "An error occurred when
you requested this page."

I have at the beginning of the page:

<cfquery name="Results" DATASOURCE="db">
select C.Title, C.SubEvent_id, P.FirstName, P.LastName, P.title, S.Title,
C.contribution
from Contrib C, People P, Sub S
where ....

[Dan Bracuk]

Originally posted by: cakesy
Thanks for your reply.

I tried doing

<CFOUTPUT> #p.title#</CFOUTPUT>

but that would no work. it comes up with an error page "An error occurred when
you requested this page."

I have at the beginning of the page:

<cfquery name="Results" DATASOURCE="db">
select C.Title, C.SubEvent_id, P.FirstName, P.LastName, P.title, S.Title,
C.contribution
from Contrib C, People P, Sub S
where ....


In your select clause, give every field an alias if you qualify with a table
name or alias. In your case, that's all of them. Afterwards, refer to the
aliases.

[paross1]

What they are suggesting is to alias the columns with the same names in the
query so that you can use the new column name in your result:

<cfquery name="Results" DATASOURCE="db">
select C.Title,
C.SubEvent_id,
P.FirstName,
P.LastName,
P.title AS Ptitle,
S.Title AS Stitle,
C.contribution
from Contrib C, People P, Sub S
where ....

Now your fields will be Ptitle and Stitle....etc.

Phil

[mattapple]

SELECT *
FROM People, Reports
WHERE People.id = Reports.id

[paross1]

mattapple,

Your suggestion isn't really helpful in this particular situation, since if
you SELECT *, which will select the columns from both tables, you will still
have two columns with the same name (title) in the output, which is what is
causing the problem in the first place. That is why it is necessary to alias
the two like named columns in order to be able to differentiate them in the
output.

Phil

[cakesy]

Ah, excellent. This is exactly what I wanted, thanks guys. Had real trouble searching for this one.

cakesy