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.
Unsolved 
