4 Replies Latest reply on Oct 5, 2007 12:41 PM by paross1

    Problem Outputting Data From Second Table Based On ID in FIrst Table

    Matthew@certifiedwebpro
      Hi Everyone: I have been stuck for a couple of days on a couple of issues. Here is one:

      I have a COURSES table. I also have a COURSES Summary page that displays all of the courses and related info.

      When a user clicks on a specific course it takes them to a courses detail page for that specific course. PROBLEM: Everything is working ok except: I have a separate table that has the teachers information in it. The two tables are related by The Primary Key (PERSONNEL_ID) in the Teachers table (Called PERSONNEL_LISTS) and MT_ID in the COURSES TABLE. I don't want the MT_ID to be displayed on the details page: I want the teachers first and last name.



      Here are the two tables:

      http://www.coolflasher.com/Courses_table.gif

      http://www.coolflasher.com/Personnel_table.gif

      The SQL that is currently being used on the Course Details page is:

      <cfparam name="URL.COURSE_ID" default="-1">
      <cfparam name="URL_COURSE_ID" default="#URL.COURSE_ID#">
      <cfparam name="SESSION._Insert_COURSES" default="-1">
      <cfquery name="COURSES" datasource="SCHEDULING_TRACKER">
      SELECT *
      FROM COURSES
      WHERE COURSE_ID = <cfqueryparam value="#URL.COURSE_ID#" cfsqltype="cf_sql_numeric"> OR ( -1= <cfqueryparam value="#URL_COURSE_ID#" cfsqltype="cf_sql_numeric"> AND COURSE_ID=<cfqueryparam value="#SESSION._Insert_COURSES#" cfsqltype="cf_sql_numeric">)
      </cfquery>

      And I'm outputting the MT_Code: <cfoutput>#COURSES.MT_ID#</cfoutput>

      I tried adding the PERSONNEL_LISTS table to the query, but I can't figure out how to get it to output the FIRST_NAME and LAST_NAME column that correspond with the MT_ID in the COURSES table. Courses Table
        • 1. Re: Problem Outputting Data From Second Table Based On ID in FIrst Table
          Dan Bracuk Level 5
          Don't use select *.

          The two most common ways to join tables are:

          from table1 join table2 on table1.somefield = table2.somefield
          and
          from table1, table2
          where table1.somefield = table2.somefield

          Your sample code has neither.
          • 2. Problem Outputting Data From Second Table Based On ID in FIrst Table
            paross1 Level 2
            Something like this maybe?

            (NOTE: you should explicitly list column names from both tables instead of using *)

            <cfquery name="COURSES" datasource="SCHEDULING_TRACKER">
            SELECT t.FIRST_NAME,
            t.LAST_NAME,
            c.COURSE_NAME,
            c.COURSE_ID,
            ---whatever other fields that you want to select
            FROM COURSES AS c
            INNER JOIN PERSONNEL_LISTS AS t ON c.MT_ID = t.PERSONNEL_ID
            WHERE c.COURSE_ID = <cfqueryparam value="#URL.COURSE_ID#" cfsqltype="cf_sql_numeric">
            OR ( -1 = <cfqueryparam value="#URL_COURSE_ID#" cfsqltype="cf_sql_numeric">
            AND c.COURSE_ID=<cfqueryparam value="#SESSION._Insert_COURSES#" cfsqltype="cf_sql_numeric">)
            </cfquery>

            Also, not sure what you are expecting -1 = <cfqueryparam value="#URL_COURSE_ID#" cfsqltype="cf_sql_numeric"> to do.

            Phil
            • 3. Re: Problem Outputting Data From Second Table Based On ID in FIrst Table
              Matthew@certifiedwebpro Level 1
              Thanks for the help Dan and Phil, I appreciate it.

              I was able to get it to work with Dans suggestion of:

              from table1 join table2 on table1.somefield = table2.somefield

              (I used FROM dbo.COURSES LEFT JOIN dbo.PERSONNEL_LISTS ON COURSES.MT_ID = PERSONNEL_LISTS.PERSONNEL_ID)

              I discovered that it worked fine except no record was returned if the MT_ID record was empty in the PERSONNEL_LISTS table. Once I discoved the "LEFT" Join, it works perfect now.

              Also, Just wondering do you suggest specifying each column name that is to be queried instead of a blanket "*" mainly for database efficiency reasons? (For if a lot of folks are using the database) or is there another reason?

              Thanks again, I appreaciate it!
              Matthew
              • 4. Re: Problem Outputting Data From Second Table Based On ID in FIrst Table
                paross1 Level 2
                Efficiency is one consideration, but it is especially important if you attempt to select columns from more than one table that may have the same column name. Not only would you have to append a table name or table name alias to the column name to avoid the ambiguous refrence, but you would also need to alias the column name itself so that you could tell the different columns from one another in the output. You can't do that if you are selecting *.

                As for efficiency, why would you want ot return a lot of unecessary data from your database to your ColdFusion server? If your table has 10 columns, but you only need to select 3, that is a lot of "wasted" bytes being transferred over the network, etc.

                Phil