Professional Web Applications Themes

Listing join from tables... - Coldfusion Database Access

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... :-) ...

  1. #1

    Default Listing join from tables...

    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




    createmedia Guest

  2. #2

    Default Re: Listing join from tables...

    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

    paross1 Guest

  3. #3

    Default Re: Listing join from tables...

    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 Guest

  4. #4

    Default Re: Listing join from tables...

    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>

    elDonrico Guest

  5. #5

    Default Re: Listing join from tables...

    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>



    paross1 Guest

  6. #6

    Default Re: Listing join from tables...

    Thanks all for your help....

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

    Thanks again!
    createmedia Guest

Similar Threads

  1. Replies: 5
    Last Post: June 27th, 06:35 AM
  2. Listing Tables IN CF script
    By maxu$ in forum Coldfusion Database Access
    Replies: 1
    Last Post: August 25th, 10:24 PM
  3. New to Joines - Inner Join on 4 Tables
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 14th, 03:14 PM
  4. HELP.. Recordset JOIN tables
    By Addy_31 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 01:51 AM
  5. SELECT from multiple tables (not join though)
    By Madison Kelly in forum PostgreSQL / PGSQL
    Replies: 15
    Last Post: January 10th, 07:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139