complex query issue

[mmonclair]

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.

I've attached the code that I used to define the query and its implementation, in its latest version. 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.

[Dan_Bracuk]

Have you considered a left join to a subquery?

select yourfields
from program left join (
select morefields
from exam
where it's the session user
) aliasrequired on qdprogramnumber = aliasrequired.programid
where anycontstraints on program you might have.

[mmonclair]

Thanks much for your response. Unfortunately, it's not working. I get the following error:

Error Executing Database Query.
Join expression not supported.

The error occurred in E:\Inetpub\wwwroot\Nursing\conweb\OE_programentry3a.cfm: line 91

89 : SELECT *
90 : FROM exam
91 : WHERE useraccount = '#session.userid#'
92 : )
93 : ON QDProgramNumber = program_id

When I comment out the offending Line 91 (which works fine in simple queries, but also seemed to give me trouble in another join I tried elsewhere, even when I aliased the fields), I got this message:

Error Executing Database Query.
Syntax error in JOIN operation.

The error occurred in E:\Inetpub\wwwroot\Nursing\conweb\OE_programentry3a.cfm: line 85

85 : <cfquery name="test" datasource="concne">
86 : SELECT
87 : QDProgramNumber,

I then tried setting up each subquery separately and then doing this as a query of queries. I got this error message:

Error Executing Database Query.
Query Of Queries syntax error.
Encountered "LEFT.

The error occurred in E:\Inetpub\wwwroot\Nursing\conweb\OE_programentry3a.cfm: line 68

68 : <cfquery name="test" dbtype="query">
69 : SELECT
70 : QDProgramNumber,

I tinkered with both approaches, trying to match your example as well as the syntax in the join code I had that did work.

[Dan_Bracuk]

Line 91 has to come after the subquery. You appear to have it before.

[mmonclair]

No, I have it following. Here's the full query:

<cfquery name="test" datasource="concne">
SELECT *
FROM program
LEFT OUTER JOIN (
SELECT *
FROM exam
WHERE useraccount = '#session.userid#')
ON program.QDProgramNumber = exam.program_id
ORDER BY QDProgramNumber
</cfquery>

I tried using statement "LEFT JOIN" and "LEFT OUTER JOIN", with the same result. If I make the fields more specific, I have the following query, with the same result:

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

I'm also not understanding why, when I comment out the WHERE statement, my opening tag throws an error.

[-__cfSearching__-]

mmonclair wrote:
> No, I have it following. Here's the full query:

Take a closer look at Dan's first suggestion. You left out the alias on the derived table.

> 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

AFAIK outer joins are not supported with QoQ, but they are perfectly acceptable in a database query.

[mmonclair · Accepted solution]

I got it to work, this is the query 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>

After a few attempts to get this to work in CF, I started tinkering with it in my db and got it to work. Thanks much for your help!

[Dan_Bracuk]

search for the string "required" on this web page.