Inner join issue

[knightjb1]

I having this issue when i use an innerjoin in the cfquery tag. I am trying to join my two tables on a certain id then when all is said and done i need to spit out the ID to the page. My query is

<cfquery name="getQuestion" datasource="DATES">
SELECT question.questionID, question.quizID, quiz.quizID, quiz.quizName From question
INNER JOIN quiz ON question.quizID = quiz.quizID
WHERE quizName = '#qName#'
</cfquery>

I tested the query in access and it works fine i just cant get it to let me print the iD it keeps telling me its undefined. Anyone know why this is?

 

[SQLSister]

This does not appear to be inthe right forum. SQL Server and Access are two differnt programs and that is certainly not SQL Server code.

"NOTHING is more important in a database than integrity." ESquared

 

[foxdev]

It could be because you'll end up with two QuizID columns, and your database system might be assigning aliases to them like QuizID_a, QuizID_b.

Try assigning the aliases yourself:

SELECT question.questionID, 
question.quizID as quizID, quiz.quizID as quizID2, quiz.quizName From question
INNER JOIN quiz ON question.quizID = quiz.quizID
WHERE quizName = '#qName#'

Since you're joining on QuizID, I can't see any reason to ask for both in the result set to begin with.

--------------

http://www.foxdev.com