Joe, Sorry, but to add to your solution another problem ( i don't do this on purpose, promise). It will also return 0 where the is no user there, in which case you get a false positive.
Mark, you'd have to do the counts as 2 different SQL's statements which was something i was trying to avoid. I've now created a UDF which checks the occurance of a word in a string/list. So with the SQL SELECT userId WHERE userId = 'xx' I am using ValueList() and my UDF for checking that the occurance of pass equals the recordcount. On Mon, 20 Dec 2004 13:45:59 +0100, Mark Drew <[EMAIL PROTECTED]> wrote: > Or you could Count(mark) where it equals fail and count where it > equals pass total them and there you go :D > > > On Mon, 20 Dec 2004 07:41:15 -0500, Joe Rinehart <[EMAIL PROTECTED]> wrote: > > Hey Andy, > > > > Instead of seeing if they're all "Pass," why not just see if there are > > any failed? For user 1: > > > > <cffunction name="userFailed" returntype="boolean"> > > <cfargument name="qGrades" type="query"> > > <cfargument name="userId" type="numeric"> > > <cfset var qGetGrades = "" /> > > > > <cfquery dbType="query" name="qGetGrades"> > > SELECT userId > > WHERE grade = 'fail' > > AND userId = <cfqueryparam cfsqltype="cf_sql_int" > > value="#arguments.userId#"> > > </cfquery> > > > > <!--- Return 0 if there are no failures, int other than 0 (true) if > > there is a failure ---> > > <cfreturn qGetGrades.recordCount /> > > </cffunction> > > > > -joe > > > > On Mon, 20 Dec 2004 12:32:12 +0000, Andy Jarrett <[EMAIL PROTECTED]> wrote: > > > Opps badly worded post sorry. > > > > > > Im trying to create a QoQ SQL statement that would return whether user > > > 1 has passed or failed by checking that all his grades are passes? > > > > > > I've just been playing with another method were i am using the QoQ to > > > return the users marks then just by checking that the amount of pass > > > marks equals the amount of entries for the user. > > > > > > Hope this makes sense > > > > > > Andy J > > > > > > > > > On Mon, 20 Dec 2004 13:23:04 +0100, Mark Drew <[EMAIL PROTECTED]> wrote: > > > > Whats the question? > > > > > > > > > > > > On Mon, 20 Dec 2004 11:50:34 +0000, Andy Jarrett <[EMAIL PROTECTED]> > > > > wrote: > > > > > Hi there, > > > > > > > > > > I am have a table with user id and grade SQL statement. THe Table is - > > > > > > > > > > userid | grade > > > > > 1 | pass > > > > > 1 | pass > > > > > 1 | pass > > > > > 2 | fail > > > > > 2 | pass > > > > > 3 | pass > > > > > 4 | pass > > > > > 4 | fail > > > > > 5 | pass > > > > > > > > > > The output shows bascially whether the user has passed all courses. So > > > > > user 1 would be an overall 'Pass' mark as all courses are completed. > > > > > Yet user 2 would be an overall 'Fail' as he/she has one more course to > > > > > pass. > > > > > > > > > > Oh and this is a QoQ that i have to do this in. > > > > > > > > > > Any help appreciated > > > > > > > > > > Regards > > > > > > > > > > Andy J > > > > > www.andyjarrett.co.uk > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188231 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54