Results 1 to 9 of 9

Thread: SQL Query Help

  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Unanswered: SQL Query Help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use LEFT OUTER JOINs

    since you want all persons, that has to be your left table
    Code:
    SELECT tblperson.personID
         , tblperson.personFirstName
         , tblperson.personLastName
         , tblperson.personInstructor
         , tblpool.Pool
         , 100.0 * COUNT(tblinteraction.interactionResult) / PoolMaxScore AS 'Pool Percentage'
      FROM tblperson
    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
     WHERE tblperson.personLastName = 'Sermon'
       AND tblperson.personFirstName = 'Erik'
    GROUP 
        BY tblperson.personID
         , tblpool.Pool
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    6
    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?

  4. #4
    Join Date
    Sep 2011
    Posts
    6
    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!!!!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AntonZdz View Post
    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.
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2011
    Posts
    6
    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!!!!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AntonZdz View Post
    #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
    you need a space between OUTER and JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2011
    Posts
    6
    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

  9. #9
    Join Date
    Sep 2011
    Posts
    6
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •