Hi All

I have the below SQL query that returns almost all of the information I need but not quite. The query is used to return a score (in percentage form) that a person gets when taking a quiz. Each quiz is split up into different pools and the query is grouped by this. Some pools only have one or two questions in and because of my where interactionreult = 'C' (correct answers), if a pool has no correct answers I wont get a result for that pool, how can I set it to show a zero percentage if the person has no correct results. I was thinking I could do a sum which would basically be 'SUM ( '0' + (count(where interactionresult = 'C'))', but I cant figure out the syntax for this! My current query is as follows.

SELECT
`tblinteraction`.`personID`,
`Pool`,`tblperson`.`personFirstName`,`tblperson`.` personLastName`,`tblperson`.`personInstructor`,
COUNT(`interactionResult`)/`PoolMaxScore` * 100 as 'Pool Percentage'
FROM `tblinteraction`
INNER JOIN `tblpool` on `PoolName` = `Pool`
INNER JOIN `tblperson` on `tblperson`.`personID` = `tblinteraction`.`personID`
WHERE
`interactionResult` = "C"
AND
`personLastName` = "Sermon"
AND
`personFirstName` = "Erik"
AND
`quizNumber` = "1"
GROUP BY `Pool`, `tblinteraction`.`personID`

More than happy to do it another way if anyone can think of it!!!

Hope someone can help!!!

Cheers

Anton

use LEFT OUTER JOINs

since you want all persons, that has to be your left table

Thanks for the quick reply, I will get onto the database and try this out!

Only one question though, i am not sure if I made my first post clear enough, I am not after returning all people, I am after returning all pools for that quiz.

My old query showed the right people (there is only one person with each name so the where query for returning that first works ok)

So perhaps If I take your advice and use pool as my left table this will have the same effect?

Would an example set of data then an example set of the result I get currently and then an example of what I would like be helpful at all?

Hi All

I have tried running the suggested query but It wont run, the problem with it appears to be at my left outer joins.

LEFT OUTER
JOIN tblinteraction
ON tblinteraction.personID = tblperson.personID
AND tblinteraction.quizNumber = 1
AND tblinteraction.interactionResult = 'C'
LEFT OUTER
JOIN tblpool
ON tblpool.PoolName = tblinteraction.Pool

On the first join I don't have a tblinteraction.quiznumber, that value is stored in tblpool, however if I adjust it to tblpool.quiznumber, then it fails as it isn't joined to that table first.

So if I try it in the other order, with the amended quiz number name, that fails because tbl interaction hasn't been joined yet.

LEFT OUTER
JOIN tblpool
ON tblpool.PoolName = tblinteraction.Pool
LEFT OUTER
JOIN tblinteraction
ON tblinteraction.personID = tblperson.personID
AND tblpool.quizNumber = 1
AND tblinteraction.interactionResult = 'C'

I will try and work out how to use pool as my left table

Thanks for the help so far!!!!

in post #2, where i rearranged the joins, i had to make several guesses as to which table certain columns were in

this is because in post #1, you qualified some of the columns, like tblperson.personInstructor, but failed to properly qualify others, like PoolName and Pool

so maybe i guessed wrong, eh

anyhow, you're on your way...

Right all

I have tried to re write the query I was suggested, so that it used pool as its left table (since I always want a result for each pool in a quiz even when it is zero) but I get the below error when I try to run it.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTERJOIN `tblinteraction`ON `tblinteraction`.`Pool` = `tblpool`.`PoolName`AND `' at line 1

My new query is as below.

SELECT `tblinteraction`.`personID`
,`tblperson`.`personFirstName`
,`tblperson`.`personLastName`
,`tblperson`.`personInstructor`
,`tblinteraction`.`Pool`
,100.0 * COUNT(`tblinteraction`.`interactionResult`)/`tblpool`.`PoolMaxScore` AS 'Pool Percentage'
FROM `tblpool`
LEFT OUTER
JOIN `tblinteraction`
ON `tblinteraction`.`Pool` = `tblpool`.`PoolName`
AND `tblinteraction`.`quizNumber` = "1"
AND `tblinteraction`.`interactionResults` = "C"
LEFT OUTER
JOIN `tblperson`
ON `tblperson`.`personID` = `tblinteraction`.`personID`
WHERE `tblperson`.`personFirstName` = "Erik"
AND `tblperson`.`personLastName` = "Sermon"
GROUP BY `tblinteraction`.`Pool`, `tblinteraction`.`personID`

Am I barking up the wrong tree here? My original query very nearly did the trick it just didn't show results when there weren't any correct answers for that pool.

Once again, thanks for the continued help!!!!

you need a space between OUTER and JOIN

Thanks for that

I have amended the query accordingly and it now runs, however it still only returns those pools that have correctly answered questions. Any pools that either have no answers or only incorrect answers are ignored . As an example, either my original query or this new and improved query returns 3 rows as below (columns are in order from left to right 'PersonID','FirstName','LastName','Instructor','Po olName','Pool Percentage')

1 Erik Sermon Rob Mills Armouring 50.00000
1 Erik Sermon Rob Mills End Preparation UJ 25.00000
1 Erik Sermon Rob Mills X-Ray 100.00000

I want it to return (as an example, assuming no other questions are correct, excuse the very bad cut and paste!)

1 Erik Sermon Rob Mills Armouring 50.00000
1 Erik Sermon Rob Mills End Preparation UJ 25.00000
1 Erik Sermon Rob Mills X-Ray 100.00000
1 Erik Sermon Rob Mills Chemical & Adhesives 0
1 Erik Sermon Rob Mills Equipment UJ 0
1 Erik Sermon Rob Mills General & Safety 0
1 Erik Sermon Rob Mills Joint Assembly UJ 0
1 Erik Sermon Rob Mills Joint Closure UJ 0
1 Erik Sermon Rob Mills UJ Tool Kits 0
1 Erik Sermon Rob Mills Moulding 0
1 Erik Sermon Rob Mills Fibre Splicing 0
1 Erik Sermon Rob Mills Tools UJ 0
1 Erik Sermon Rob Mills Wrapping 0

I have been trying all day without success to get this to work, It appears to be related to multiple pools that is causing the issue. For example if I run the below query I get a 0.000 displayed as a value ( this is what I want).

SELECT COUNT(`interactionResult`) * 1/`PoolMaxScore` * 100 as 'Pool Percentage' FROM `tblinteraction` INNER JOIN `tblpool` on `PoolName` = `Pool`WHERE `interactionResult` = "C" AND `personID` = "2"AND `PoolID` = "1"

So this is counting the number of rows where interaction result is C and finding none and so displaying 0.000. However as soon as I introduce multiple pools I get nothing returned at all (other than 'MySQL returned an empty result set (i.e. zero rows)'), even if I add a group by, I still get nothing, all I need i for the query to also return a zero when the count doesn't return any rows, could I do this with an IF statement?