complex query issue

I'm having some difficulty with a query I'm trying to set up for a page I'm setting up to show online test takers their status in relation to a list of programs my department offers online. 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 (while all the documentation I've read says a left outer join should not be able to work in CF, I've actually had good luck with this for related tables I've queried in other pages). 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 in nailing down the problem.

 

Can you post samplet table data, the results you are getting, and the results you want?

 

Here's a sample of the query I mentioned, the results I got and the results I want. I abridged the list to take out long series of rows that currently have no results for the sample user.



Under the "Desired Result" side, each record marked "Completed" represents a test that the sample user has taken. On the left, for each corresponding record that is blank, there are records from a number of users. Only for those records for which the user has the only record in the DB, does the "Completed" entry appear.

One hurdle I've been having, which may affect this, is that in the query, in every variation I've used, an error is thrown if I constrain the search from the "exam" table by the session user (ie. WHERE tblLearnerExam.useraccount = '#session.userid#'). I've tried to work around this by putting a conditional statement in the cfloop like so:

<cfif IsDefined('program_id') AND useraccount EQ '#session.userid#' >

Incidentally, if I don't set a group attribute in the cfloop tag, I get a row for each record with the program_id brought in by the query. If I can figure out how to constrain the joined table by the session user, I think this will put me on my way to getting this to work properly.

 

Even if it did work, that would basically change your query to an INNER join. Probably not what you want. Out of curiosity what database are you using and what is the error?

Have you tried simply adding it to your ON clause?

LEFT OUTER JOIN exam
ON program.QDProgramNumber = exam.program_id
AND tblLearnerExam.useraccount = '#session.userid#'

 

... Using the real table names (whatever those are) ;-)

 

I wouldn't think it would, if the WHERE statement followed the LEFT OUTER JOIN one. I had also tried doing a subquery in my join statement like so...

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

...but it keeps getting hung up on that constraint. I am querying a MS Access DB (again, I only inherited this). This is the error message I get:

 Error Executing Database Query.
Syntax error in FROM clause.
 
The error occurred in E:\Inetpub\wwwroot\Nursing\conweb\OE_programentry3a.cfm: line 65

63 : 	FROM qdprogram 
64 :     LEFT OUTER JOIN tblLearnerExam
65 : 	WHERE tblLearnerExam.useraccount = '#session.userid#'
66 :     ON qdprogram.QDProgramNumber = tblLearnerExam.program_id 
67 :     ORDER BY qdprogram.QDProgramNumber

Code (markup):

Yes, what I end up with is only those records that correspond to the session user.

 

Yes, it would. Because you are using an outer join, the "exam" fields will be null if there is no match. But the WHERE clause cancels out those nulls by saying, "only return the ones where useraccount = '#session.userid#'.

That is what I thought. Unfortunately, Access is less flexible and less tolerant. You have to find a syntax that it will accept. (You might even resort to using the query builder tool to find the right combination.).

I don't know if Access will allow it, but with that method you have to give the subquery an alias. You are also missing the ON ... clause there.

That does not sound right. Let me try it.

 

It works fine. I think you have been looking at it too long and have some of your table aliases mixed up.

SELECT QDProgramNumber, programname
FROM qdprogram LEFT  JOIN tblLearnerExam
ON (qdprogram.QDProgramNumber = tblLearnerExam.program_id
AND tblLearnerExam.useraccount = '123')
ORDER BY qdprogram.QDProgramNumber

Code (markup):

 

I have definitely been staring at this too long

I tried it this way, and I do not get any information pulled in from the exam table. I also went back and tried this query :

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

It still hangs up on that WHERE statement. When I comment it out or use a value in any record of that field, it then throws an error on the opening tag. Just for kicks, I tried this query in Access directly. It will not accept it, even though it is OK by its own documentation (I did try your example, and I got the same result as what appeared on my CF page). So this tells me that I'm having a problem with MS Access instead of CF, and that is where I will need to address it.

Thanks much for your help. If I get this working, I'll post my solution.

 

After some more tinkering, I got it to work with this query:

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

I had not aliased my QDProgramNumber fields and it appears that Access will not accept single quotes in its field value definition.

thanks much for your help!

 

Weird. Usually with cfquery you have to use single quotes, not double. All I can say is I ran the query within Access 2003 and it worked fine. So I am not sure what happened.. except to say it is Access {shrug} ;-) I inherited a project like that once too. It was often frustrating because some queries I _knew_ were valid with other databases just plain wouldn't run in Access, or needed special syntax. Arrghh!

Glad you finally got it up an running though!

 

I can't stand doing SQL in Access. Anytime I see access, I lobby for SQL server or MySQL. $0.02.

 

I hear you, and wholeheartedly agree. I still have flashbacks from that one project where I was forced to use it. If you have any choice at all, upgrade to a better database. If not .. you have our sympathies ;-)