CODEWITHSUNDEEP
mysqlsqldatabase
heron
heron

Reputation: 3675

MySQL query return unexpected values

Need to generate courses list and count

  1. all
  2. unanswered
  3. answered but unchecked

Questions.

My database structure is looking like that

https://docs.google.com/open?id=0B9ExyO6ktYcOenZ1WlBwdlY2R3c

Explanation for some of tables:

  1. answer_chk_results - checked answers table. So if some answer doesn't exist on this table, it means it's unchecked
  2. lesson_questions - lesson <-> question associations (by id) table
  3. courses-lessons - courses <-> lessons associations (by id) table

Executing 

    SELECT
    c.ID,
    c. NAME,
    COUNT(lq.id) AS Questions,
    COUNT(
        CASE
        WHEN a.id IS NULL THEN
            lq.id
        END
    ) AS UnAnswered,
    COUNT(
        CASE
        WHEN cr.id IS NULL THEN
            lq.id
        END
    ) AS UnChecked
FROM
    courses c
LEFT JOIN `courses-lessons` cl ON cl.cid = c.id
LEFT JOIN `lesson_questions` lq ON lq.lid = cl.lid
LEFT JOIN answers a ON a.qid = lq.qid
LEFT JOIN answer_chk_results cr ON cr.aid = a.id
GROUP BY
    c.ID

Tested it first on SQL fiddle with sample data. (Real data is huge, so I can't place it on sqlfiddle) It returned some values. Thought works well. But while I test it with real data, see that returns wrong values. Forex, when I manually count, result for all questions count must be 25, but it returns 27. Maybe I'm doing something wrong.

Note MySQL server running on my local machine, so I can give you teamviewer id and password if you want to connect to my desktop remotely and test query with real data.

Upvotes: 1

Views: 168

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I suspect the problem is that different joins are resulting in a multiplication of rows. The best way to fix this is by using subqueries along each dimension. The following is a more practical way. Replace the COUNTs in the select with COUNT DISTINCT:

SELECT c.ID, c. NAME,
       COUNT(distinct lq.id) AS Questions,
       COUNT(distinct CASE WHEN a.id IS NULL THEN lq.id END) AS UnAnswered,
       COUNT(distinct CASE WHEN cr.id IS NULL THEN lq.id END) AS UnChecked

Compared to COUNT, COUNT DISTINCT is a resource hog (it has to remove duplicates). However, it will probably work fine for your purposes.

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Use this query

SELECT
    c.ID,
    c.NAME,
    COUNT(lq.id) AS Questions,
    COUNT(IFNULL(a.id),lq.id)AS UnAnswered,
    COUNT(IFNULL(cr.id),lq.id)AS UnChecked,
FROM    courses c
LEFT JOIN `courses-lessons` cl ON cl.cid = c.id
LEFT JOIN `lesson_questions` AS lq ON lq.lid = cl.lid
LEFT JOIN answers a ON a.qid = lq.qid
LEFT JOIN answer_chk_results cr ON cr.aid = a.id
GROUP BY c.ID

Upvotes: 0

Related Questions