RecordCount Output 1

[rojas1mg]

Folks,
I need some help. I have a query that outputs properly in SQL Query Analyzer but doesn't output properly in CFML.

Here's my query:

<!---This query will count the number of seats available--->
<cfquery name="countseats" datasource="#dsource#">
	SELECT c.course_id, c.course_label, c.course_instructor, c.course_location, c.course_hours, c.course_seats, #getcourses.course_seats# - Count(r.course_id) as seats_left
	FROM tbl_course C
	INNER JOIN tbl_registration R on c.course_id = r.course_id
	WHERE r.course_id = #getcourses.course_id# AND course_disabled=0
	GROUP BY c.course_id, c.course_seats, c.course_label, c.course_instructor, c.course_location, c.course_hours
</cfquery>

PS:....I've tried INNER JOIN, LEFT JOIN & RIGHT JOIN....

Now the output line of code:

		<TD align="center"><cfif countseats.seats_left eq 0><font color="##FF0000"><B>Full</B></font><cfelse>#countseats.seats_left#</cfif></TD>

Here's what goes on. If a class was created with a 10 seat limit and no one registered, it does not display the number 10 or 25 or whatever was set when the class was created. However, when a person DOES register for the class, now the 10 seat class displays 9 and so on. I basically get a blank cell when no one has registered.

I'm not sure if this is a SQL question and should I post it out there? I thought it was a CFML question which is why I posted here.


rojas1mg - - - I love Tek-Tips and all members who reply.

 

[webmigit]

<TD align="center"><cfif countseats.seats_left eq 0><font color="##FF0000"><B>Full</B></font><cfelse>#[red]val([/red]countseats.seats_left[red])[/red]#</cfif></TD>

Val examines a variable and if it is not a number, it returns 0, if it is, it returns the number.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[rojas1mg]

That was cool, however, it must be my syntax in my query now. It now displays 0 in those columns versus however many seats are actually available.

rojas1mg - - - I love Tek-Tips and all members who reply.

 

[webmigit]

Hmm... Try...

#max(countseats.seats_left,countseats.course_seats)#

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[rojas1mg]

I've made the line of code look like this:

		<TD align="center"><cfif countseats.seats_left eq 0><font color="##FF0000"><B>Full</B></font><cfelse>#max(countseats.seats_left,countseats.course_seats)#</cfif></TD>

And now the error is as follows:

The value "" cannot be converted to a number

rojas1mg - - - I love Tek-Tips and all members who reply.

 

[webmigit]

<TD align="center"><cfif countseats.seats_left eq 0><font color="##FF0000"><B>Full</B></font><cfelse>#max(val(countseats.seats_left),val(countseats.course_seats))#</cfif></TD>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[rojas1mg]

Well, that gave me the max number of seats available for the courses that had people registered in them and not the actual number of seats left. But for those with no one registered, it still shows zero.

rojas1mg - - - I love Tek-Tips and all members who reply.

 

[rojas1mg]

Here's the other query on the page that might help.

<!---This query will be used to gather the course information displayed below--->
<cfquery name="getcourses" datasource="#dsource#">
	SELECT *
	FROM tbl_course
	WHERE COURSE_DT >= '#DateFormat(Now(), "mmm dd yyyy")#' and course_disabled = 0
	ORDER BY course_dt
</cfquery>
[/code

rojas1mg - - - I love Tek-Tips and all members who reply.

 

[TruthInSatire]

I think its a sql problem webmigit.

they way it's joining I don't think course_seats is returning if there aren't any records in the registration table, i.e. no students. joining isn't my strong point so i can't input much more than that.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[webmigit]

Not great on joining either... I've not been doing very hot today, a few logic errors.. bleah.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[TruthInSatire]

I played around in access and came up with this which works good. you'd just change 3 to #getcourses.course_id# maybe if rudy is around he can tell you how to make both queries into one.

SELECT class.maxSeats, class.maxSeats-Count(registrations.courseID) AS seats_left, class.courseID
FROM class LEFT JOIN registrations ON class.courseID = registrations.courseID
GROUP BY class.maxSeats, class.courseID
HAVING (((class.courseID)=3));

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)