Listing join from tables...

[createmedia]

Hello all,

I have a page with query from two tables... And am having problems listing
values out of both tables...
The query is :

<cfquery name="dateInfo" datasource="flawlessdb">

SELECT CAprojectDB.ProjectID, CPproduction.ProjectID, CPproduction.apptDate,
CPproduction.servicesPerformed, CPproduction.recommendations,
CPproduction.notes

FROM CAprojectDB,CPproduction

WHERE CAprojectDB.ProjectID = CPproduction.ProjectID

ORDER BY apptDate DESC

</cfquery>


and the output is :

<cfoutput query="dateInfo">
<tr height="20">
<td valign="top" align="center"><div class="pagestyle">
<a href="apptRecDetail.cfm?ID=#URLEncodedFormat(ID)#" >#CAprojectDB.FirstName#
#CAprojectDB.LastName# </a>
</div></td>
<td valign="top"><div class="pagestyle">#DateFormat(apptDate)#</div></td>
<td valign="top"><div class="pagestyle">#servicesPerformed#</div></td>
</tr>
</cfoutput>

The error says :
"Element FIRSTNAME is undefined in CAPROJECTDB."

but I'm sure the field is in the table....
Any help you can give..... greatly appreciated...
:-)
John

[paross1]

Why are you selecting projectID from both tables in your query when you should
be selecting the FirstName and LastName columns instead. Also, you shouldn't
use the table prefix on the column name in the output, as this is only used in
the SQL to help the database "know" which table to associate with which column.

<cfquery name="dateInfo" datasource="flawlessdb">
SELECT CAprojectDB.FirstName, CAprojectDB.LastName, CPproduction.apptDate,
CPproduction.servicesPerformed, CPproduction.recommendations,
CPproduction.notes
FROM CAprojectDB,CPproduction
WHERE CAprojectDB.ProjectID = CPproduction.ProjectID
ORDER BY apptDate DESC
</cfquery>
...blah blah...
<a href="apptRecDetail.cfm?ID=#URLEncodedFormat(ID)#" >#FirstName# #LastName#
</a>

Phil

[elDonrico]

you need to select the FIRSTNAME out of the table too...

<cfquery name="dateInfo" datasource="flawlessdb">

SELECT CAprojectDB.ProjectID, CAprojectDB.FIRSTNAME, CAprojectDB.LASTNAME,
CPproduction.ProjectID, CPproduction.apptDate, CPproduction.servicesPerformed,
CPproduction.recommendations, CPproduction.notes

FROM CAprojectDB,CPproduction

WHERE CAprojectDB.ProjectID = CPproduction.ProjectID

ORDER BY apptDate DESC

</cfquery>


plus you need to join the tables.

[elDonrico]

sorry, i failed to see your outer join there, but more preferably, i would
write it like this...

<cfquery name="dateInfo" datasource="flawlessdb">

SELECT ca.ProjectID, ca.FIRSTNAME, ca.LASTNAME, cp.ProjectID, cp.apptDate,
cp.servicesPerformed, cp.recommendations, cp.notes

FROM CAprojectD ca INNER JOIN CPproduction cp ON ca.ProjectID = cp.ProjectID

WHERE ***some other where parameter can go***

ORDER BY apptDate DESC

</cfquery>

[paross1]

elDonrico, the tables are "joined". In pre-ANSI-92 SQL, can list the tables in
the FROM and join them in the WHERE.


--this--
<cfquery name="dateInfo" datasource="flawlessdb">
SELECT CAprojectDB.ProjectID, CAprojectDB.FirstName, CAprojectDB.LastName,
CPproduction.apptDate, CPproduction.servicesPerformed,
CPproduction.recommendations, CPproduction.notes
FROM CAprojectDB, CPproduction
WHERE CAprojectDB.ProjectID = CPproduction.ProjectID
ORDER BY apptDate DESC
</cfquery>

--is exactly the same as--

<cfquery name="dateInfo" datasource="flawlessdb">
SELECT CAprojectDB.ProjectID, CAprojectDB.FirstName, CAprojectDB.LastName,
CPproduction.apptDate, CPproduction.servicesPerformed,
CPproduction.recommendations, CPproduction.notes
FROM CAprojectDB
INNER JOIN CPproduction ON CAprojectDB.ProjectID = CPproduction.ProjectID
ORDER BY apptDate DESC
</cfquery>

[createmedia]

Thanks all for your help....

I missed some of the obvious.... Think I've been sitting here too long :-)

Thanks again!