Retrieving only rows for which a join has a dependancy

Question

I am creating a testing system where users are allowed to re-test until they have passed. I would like to get a list, for a given UserID, of tests which are assigned to them which they have scored less than passing (100% for this example) on.

I have the following tables:

(Everything here has been adapted for simplicity, but it should all be valid still)

Users
(Generic "users" table with UserID and Name, etc...)

Tests
+--------+----------+------------------+
| TestID | TestName | OtherTestColumns |
+--------+----------+------------------+
|      1 |   Test 1 |         Blah.... |
|      2 |   Test 2 |         Blah.... |
|      3 |   Test 3 |         Blah.... |
|      4 |   Test 4 |         Blah.... |
+--------+----------+------------------+

Users_Have_Tests
  Users are assigned tests they must take with this table
+--------+--------+
| UserID | TestID |
+--------+--------+
|      1 |      1 |
|      1 |      2 |
|      1 |      3 |
|      2 |      1 |
|      2 |      2 |
|      2 |      3 |
+--------+--------+

TestResults
+--------+--------+------------+
| TestID | UserID | Percentage |
+--------+--------+------------+
|      1 |      1 |         75 |
|      1 |      1 |        100 |
|      2 |      1 |         80 |
|      2 |      1 |        100 |
|      1 |      2 |        100 |
|      2 |      2 |         75 |
+--------+--------+------------+

The following query works for me to figure out ALL tests which are assigned to a user, but I want to remove tests they have passed from this list

SELECT DISTINCT
    TestID,
    TestName
FROM
    `Users` AS u
RIGHT JOIN
    `Users_have_Tests` AS ut
    ON
        u.UserID = ut.UserID
LEFT JOIN
    `Tests` AS t
    ON
        ut.TestID = t.TestID
WHERE UserID = 1

I feel like I need a subquery here. I came up with the following query to find the max scores for each test a user has taken, but I'm not quite sure how to tie them together. (Subqueries are very new to me and I'm still trying to understand them.)

SELECT DISTINCT
    TestID,
    MAX(Percentage)
FROM
    `TestResults`
WHERE UserID = 1
GROUP BY TestID

The results I am looking for are:

UserID = 1
+--------+----------+
| TestID | TestName |
+--------+----------+
|      3 |   Test 3 |
+--------+----------+

UserID = 2
+--------+----------+
| TestID | TestName |
+--------+----------+
|      2 |   Test 2 |
|      3 |   Test 3 |
+--------+----------+

I have tried several manners of joins and conditions, but I can't quite wrap my head around what exactly I need here. Google and StackOverflow have failed me; Likely because I'm lacking the knowledge of what to search for.

EDIT The following is what worked for me:

SELECT DISTINCT t.TestID, t.TestName
    FROM 'Users' AS u
    RIGHT JOIN 'Users_have_Tests' AS ut
        ON u.UserID = ut.UserID
    LEFT JOIN 'Tests' AS t
        ON ut.TestID = t.TestID
    WHERE t.TestID NOT IN (
        SELECT tr.TestID
            FROM TestResults AS tr
            WHERE tr.Percentage >= 100
            AND tr.UserID = 2
        )
    AND u.UserID = 2

@Calum Using a WHERE Percentage < 100 doesn't work, since if a user has tried the test a couple times before passing this will still equate TRUE. Grouping by UserID will only ever return a single row since I already have a WHERE UserID = $uid — Hobadee, Mar 10 '15 at 23:46
think i understand what you are after, I have posted an answer below, if you do want just a specific users results then just remove the `GROUP BY` statement and add your `UserID = 1` statement into the `WHERE` clause using an `AND` — Calum, Mar 11 '15 at 00:06

tau · Answer 1 · 2015-03-10T23:45:37.207

Have you tried the following?

Edit I noticed you want to see the highest score, so I created a subquery

Edit I forgot a grouping.

Also, Test needs to be joined on both the TestID and UserID.

        SELECT
            TestID,
            TestName,
            HighestScore
        FROM
            `Users` AS u
        RIGHT JOIN
            `Users_have_Tests` AS ut
            ON
                u.UserID = ut.UserID
        LEFT JOIN
        (   SELECT TestID, UserID, MAX(Percentage) AS HighestScore
            FROM `Tests`
            GROUP BY TestID, UserID         
        )AS t
            ON
                ut.TestID = t.TestID
            AND ut.UserID = t.UserID
        WHERE HighestScore < 100
        GROUP BY TestID, TestName, HighestScore

score 1 · Accepted Answer · answered Mar 11 '15 at 00:02

1

I think I understand what you are after now, give this a go:

SELECT DISTINCT
        TestID,
        TestName
    FROM
        `Users` AS u
    RIGHT JOIN
        `Users_have_Tests` AS ut
        ON
            u.UserID = ut.UserID
    LEFT JOIN
        `Tests` AS t
        ON
            ut.TestID = t.TestID
    WHERE TestID NOT IN (SELECT TestID
                         FROM TestResults
                         Where Percentage = 100)
    GROUP BY UserID

answered Mar 11 '15 at 00:02

Calum

2,110
2
22
39

Ah-ha! Very useful! I had to tweak it a *bit*, but you got me down the right path! Thanks! Here is what I used: `SELECT DISTINCT t.TestID, t.TestName FROM 'Users' AS u RIGHT JOIN 'Users_have_Tests' AS ut ON u.UserID = ut.UserID LEFT JOIN 'Tests' AS t ON ut.TestID = t.TestID WHERE t.TestID NOT IN (SELECT tr.TestID FROM TestResults AS tr WHERE tr.Percentage >= 100 AND tr.UserID = 2) AND u.UserID = 2` – Hobadee Mar 11 '15 at 05:32

Tahbaza · Answer 3 · 2015-03-11T00:59:41.510

0

I might be missing something, but why not just run the following query and process the results?

    SELECT ut.UserId, ut.TestId, t.TestName
    FROM `Users_Have_Tests` ut INNER JOIN `TestResults` r ON ut.TestId = r.TestId
      INNER JOIN `Tests` t ON t.TestId = r.TestId
    WHERE r.Percentage < 100
    ORDER BY ut.UserId, ut.TestId;

If you plan on executing this for a single user just add a WHERE clause entry to filter against a single UserId value.

edited Mar 11 '15 at 00:59

answered Mar 10 '15 at 23:08

Tahbaza

9,486
2
26
39

That could work if there is no other solution, although it would require quite a bit of coding on the backend. I would have to create an array of Tests and loop through it for each SQL row to see if I needed to add it to the array or not. Not the most elegant of solutions and certainly not the fastest. If I'm going to just process the result I would rather return the highest score per test for the user and filter in my program based on that. – Hobadee Mar 10 '15 at 23:28
maybe I'm not understanding what you're after... You want a distinct list of Users? Above you state that you want a list of all tests that are not passed. That could result in multiple rows for each user. Also, you need a user's max score on _any_ test or max score _per_ test? – Tahbaza Mar 10 '15 at 23:34
Sorry for the confusion - I'm looking for a list of all tests assigned to a specific user which the user has not yet passed. (So I can present a list to the user of tests they must still complete) The max score query I have returns the max score _per_ test. – Hobadee Mar 10 '15 at 23:51

score 0 · Answer 4 · answered Mar 10 '15 at 23:38

Tryin the having clause

after calling

MAX(Percentage) AS HighestScore

after the group by say

HAVING Highest_Score < 100

then replace 100 with whatever the minimum passing score is.. only tests without a maximum entry above passing will be returned.

http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

Retrieving only rows for which a join has a dependancy

4 Answers4