complex query issue

[mmonclair]

I'm having some difficulty with a query I'm trying to set up. What I'm trying to do is set up a query that will populate a table that shows a list of all tests offered on the site that are active, and then show the completion status of the user who's logged in.

The tests and user scores are all stored in a database, with the appropriate tables as so (names changed for simplicity):
program: list of tests by program number and title
evaluation: records of each user's responses to a pre-test survey
exam: records of each user's responses, score, and date a certificate of completion was issued upon passing

Of course, querying each is a simple matter. Setting up a join is a bit troublesome, not only because of ColdFusion's eccentricities. First, there is not a foreign key relationship between any of these tables (I didn't design this db, I only inherited it!). Second, not all of the entries in the table "program" will exist in either "evaluation" or "exam" for the user logged in for that session. The difficulty I've had in trying to join or do a Query by Query is that I can get a list of all that a user has an entry for in either or both the evaluation or exam. However, getting a result that has the master list AND this list is escaping me. At this point, I have a master list and a status for about half of the results I need, but about half are not being pulled in by the query.

Here's the query I have that is giving me the above result:

<cfquery name="user_completed" datasource="concne">
	SELECT
    	   program.QDProgramNumber,
           exam.program_id,
           program.programname,
	   exam.useraccount,
           exam.score,
           exam.CertDtd,
           exam.Cert
	FROM program 
        LEFT OUTER JOIN exam
        ON program.QDProgramNumber = exam.program_id 
        ORDER BY program.QDProgramNumber
</cfquery>

Then I use the following code to populate the table:

<cfoutput query="user_completed" group="program_id">
  <tr>
      <!--- display program number --->
      <td>#QDProgramNumber#</td>
      <!--- display program title --->
      <td>#programname#</td>
      <!--- display user_id status  --->
      <td>
         <!--- check that there is a user_id entry for each exam listed ---> 
         <cfif IsDefined('exam.program_id') AND useraccount EQ '#session.userid#' >
             <!--- a series of conditional statements related to score and date entries --->
          <cfelse>
             <!--- leave blank --->
          </cfif>
         </td>
   </tr>
</cfoutput>

This has gotten me the closest to the result I'm trying to end up with. If I do the <cfoutput> without the group, I get duplicates for every instance of each program number (including those for users not logged in), though only the result for the session user actually shows up in its appropriate line. When grouped, I think it is only showing the first or last entry it finds for all, rather than for the session user. I tried to do a query of that query to filter out the results for anyone but the session user, but the resulting records do not include those for which the session user has no entry. I can go over the other ways I've attacked this problem and the results I've gotten if it would help.

[hhamdan]

look for the duplicates just make you query output like this
<cfoutput query="user_completed" group="program_id" maxrow="1">

[mmonclair]

look for the duplicates just make you query output like this
<cfoutput query="user_completed" group="program_id" maxrow="1">

Thanks for your response. That limited the output to the first record it encountered. So instead of about 30 entries from our master list, only one shows up. I tried it also without the group="program_id" attribute, and got the same result.

[hhamdan]

look my friend, i don't know how is your login table related to your database but i think you can play with session.userid
ether by putting if statement before you output the query or by having the session.userid with query like
and where userid=session.userid
hope it help you.

[mmonclair]

I got this figured out. After a number of attempts in CF, I went back to my db and tinkered with a subquery until I came up with something it would accept. Here's what I ended up with:

<cfquery name="test" datasource="concne">
SELECT *
FROM qdprogram
LEFT JOIN (
SELECT useraccount, program_id, score, Cert, CertDtd
FROM tblLearnerExam WHERE useraccount = "#session.userid#") test2
ON qdprogram.QDProgramNumber = test2.program_id
ORDER BY qdprogram.QDProgramNumber
</cfquery>