coldfusion code problem 2

[knightjb1]

OK, I have two tables in my database one is called Quiz and one is called examlog.

The quiz table holds the name of all the possible Quizes for a user. The examlog holds the information when a user takes a quiz and is only recorded if they get 100 on it.

So Lets say i have three quizes(quiz1, quiz2, quiz3) and in my exam log i have a user recorded as passing (quiz1 and quiz2) . I want the page to display what quizes the user has passed with a star next to it to show it has been completed but I also want to display the quiz they did not take with a link so they can take it. The problem I run into is that anyway i try to program it I get the quiz name repeated several times. I tried Using left outer join, i tried putting the data into array and doing a comparison, i tried using nested loops to do a check, but all ended up in the same result, I can get it to just show me the completed quizes but I can't get it to show me the non-completed without repeating the completed quizes again

 

[philhege]

Please post the code you've used so far, including a sample data set, the SELECT statement, and the CFOUTPUT sections.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[knightjb1]

<cfquery name="chkQuiz" datasource="DATES">
SELECT examlog.examID, examlog.quizID, examlog.completed, examlog.uname, Quiz.quizID, Quiz.quizName, Quiz.certificate
FROM Quiz LEFT JOIN examlog ON Quiz.quizID = examlog.quizID
WHERE ((examlog.uname)="#usname#")
</cfquery>

<cfoutput query="chkQuiz">
<tr>
<cfif #completed# is "Yes"><td><img src="complete.jpg" width="22" /></td>
<td><a href="../quizes/index.cfm?username=#usname#&qName=#quizName#&comp=yes">#quizName#<br /></a></td>
<cfelse>
<td>&nbsp;</td>
<td><a href="../quizes/index.cfm?username=#usname#&qName=#quizName#&comp=no">#quizName#<br /></a></td>
</cfif>
</tr>
</cfoutput>

 

[GUJUm0deL]

<cfoutput query="chkQuiz" group="quizID">

See that solves the issue...

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[philhege]

<cfquery name="chkQuiz" datasource="DATES">
SELECT examlog.examID, examlog.quizID, examlog.completed, examlog.uname, Quiz.quizID, Quiz.quizName, Quiz.certificate
FROM Quiz LEFT OUTER JOIN examlog ON Quiz.quizID = examlog.quizID
WHERE ((examlog.uname)="#usname#")
</cfquery>

This brings back all records for the individual, even if there is no matching record in the exam log.


Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[philhege]

Ah, never mind, no coffee yet.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[knightjb1]

Yeah i got that to work before but the problem is it doesnt work based on username it will bring back all the records in the database not just the user i want I need it to pull the users records and pull all the quizes in the database that werent pulled for the user...GuJu i tried adding group="quizID" got an error saying it was invalid column name for group attribute and then i tried to add group="quizName" and that gave me the same thing i had before and did not print the other quizname that is is my quiz table

 

[philhege]

OK; had my coffee.

If you want exams the user didn't take, there would be no records in examlog for that user for that quiz, right? The result of a LEFT OUTER JOIN would return values of NULL for any examlog record that didn't match with the quiz. So,

SELECT Quiz.quizID, Quiz.quizName, Quiz.certificate
FROM Quiz LEFT OUTER JOIN
(select quizID, uname from examlog where ((examlog.uname)="#usname#")) ex ON ex.quizid = quiz.quizID
WHERE ex.uname is NULL

The interior query returns all quizzes the user has taken. The JOIN matches all quizzes with that result. The overall result will have NULL values in the user name field for quizzes that didn't match, IOW the quizzes not yet taken for that user.

Better, I hope.



Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[knightjb1]

Hey that works!! it returns the quiz the user didnt take was it suppose to return all the quizes? because i just wrote another query to match the quizes the user took and that will work just fine. Regardless thanks so much for the help this will make my application so much more user friendly =]

Kudos

 

[philhege]

Right, just do it without the WHERE clause. The NULL value in the user name field would indicate those quizzes that the user didn't take yet.

 

[r937]

as you have discovered, this --

FROM Quiz LEFT OUTER JOIN examlog
ON Quiz.quizID = examlog.quizID
WHERE examlog.uname = '#usname#'

works just like an inner join

reason? the WHERE clause will actually throw away all rows where the row from the left table had no match in the right table

that's because in a LEFT OUTER JOIN, unmatched rows will set all the columns from the right table in the result row to NULL

but then the WHERE clause comes along and requires one of those columns to be equal to some value, and of course NULL is never equal to anything, so all those rows (the unmatched ones) are thrown away

solution? yes, you can join to a subquery (that was very creative), but the simpler solution is like this:

FROM Quiz LEFT OUTER JOIN examlog
ON Quiz.quizID = examlog.quizID
AND examlog.uname = '#usname#'

notice the difference? again, it's a LEFT OUTER JOIN, but this time, the ON clause is more specific about which rows to match

and unmatched rows are still returned

simple, yes?




r937.com | rudy.ca

 

[knightjb1]

Hey r937 I tried the code you pasted and it gave me this error

Error Executing Database Query.
Join expression not supported.

If you want to figure out why this isn't working then be my guest but for now i will stick with the other solution =] hehe

Im running coldfusion 7 and sql server 2005

 

[r937]

join expression not supported" is a microsoft access error message, not sql server

try

FROM Quiz LEFT OUTER JOIN examlog
ON [red]([/red]
Quiz.quizID = examlog.quizID
AND examlog.uname = '#usname#'
[red])[/red]


r937.com | rudy.ca

 

[knightjb1]

That did the trick thank you for figuring that out and explaining it so nicely, I feel dumb i didnt figure that out lol. I tried every combination under the sun of that join condition and nothing...You are very helpful thanks =]