0 Replies Latest reply on Feb 20, 2013 8:03 AM by sakonnetweb

    Many to Many - display query result?

    sakonnetweb Level 1

      Greetings

       

      In a help desk app, the manager can assingn more than one technician to a project.

       

      Whe he or she assigns a project, the tech ID & project ID are inserted in a junction table. This works fine.

       

      The description, assigned date & due date field are static- the resolution (textarea) needs to be added to as the project progesses, as well as hours spent by each tech.

       

      I am running into problems displaying what the technician sees on their "contol panel".

       

      When the tech views the details of an ongoing project, I am getting cartesian product results.

       

      The tech needs to add to the resolution (textarea),  view who else is working on the project, and how many hours each has contributed.

       

      The project display page query:

       

      SELECT DISTINCT project_ID, project_num, project_desc, project_res, assign_date, assign_time, due_date, complete_dte, complete_time, assigned,

      tech_1_hours, tech_2_hours, tech_3_hours, tech_4_hours, tech_ID, junc_tech_ID, junc_project_ID

      FROM main_projects, lookup_tech, junction_project_tech

      WHERE tech_ID = #Session.user_id#

      AND project_ID = #URL.project_ID#

      AND project_ID = junc_project_ID

      AND tech_ID = junc_tech_ID

       

      And the math;

       

      <cfset 'total' = (#tech_1_hours#+#tech_2_hours#+#tech_3_hours#+#tech_4_hours#)>

       

      The project display repeats itself for every technicain-

       

       

      Any help with this would be greatly apprecaited.

       

      24 month to retirement!

       

      Norman