CODEWITHSUNDEEP
sqlmysqlsequence
user2761030
user2761030

Reputation: 1485

SQL - Find the next row ID in sequence

I have two tables TESTS and TESTS_ATTEMPTED. I am trying to output the ID for the next test to be taken in each test type (maths, english, science) for a given USER_ID.

The structure of the tables is as follows (with some sample data):

TESTS
================
 ID    TEST_NAME   TEST_TYPE
  1     Science 1   Science
  2     Maths 1     Maths
  3     Maths 2     Maths
  4     Science 2   Science

TESTS_ATTEMPTED
================
ID   TEST_ID   USER_ID 
 1     1          2 
 2     1          1 
 3     2          2  
 4     3          2

An SQL fiddle with this dummy data is here: http://www.sqlfiddle.com/#!2/43efb/2

The expected result would be (for user ID 2):

TEST_TYPE   NUM_TESTS   NUM_TAKEN   NEXT_TEST_ID    
Science       2            1         4
Maths         2            2         Null

I have been able to create a query with the number of tests taken by a user, but not sure if it is possible to output the next test type?

SELECT count(*) as num_tests_taken, 
    TEST_NAME from TEST_NAMES tn 
INNER JOIN TESTS_ATTEMPTED ta on tn.ID = ta.TEST_ID AND ta.USER_ID = 2 
GROUP BY tn.ID

How might I join the queries together and also output the next TEST ID to be taken?

Any help greatly appreciated.

Upvotes: 0

Views: 138

Answers (1)

fancyPants
fancyPants

Reputation: 51868

SELECT TEST_TYPE, COUNT(*) AS NUM_TESTS, COUNT(TEST_ID) AS NUM_TAKEN, (
        SELECT MIN(tn.ID)
        FROM TESTS tn
        LEFT JOIN TESTS_ATTEMPTED ta ON tn.ID = ta.TEST_ID
            AND ta.USER_ID = 2
        WHERE otn.TEST_TYPE = tn.TEST_TYPE
            AND ta.USER_ID IS NULL
        GROUP BY TEST_TYPE
        ) AS NEXT_TEST_ID
FROM TESTS otn
LEFT JOIN TESTS_ATTEMPTED ota ON otn.ID = ota.TEST_ID
    AND ota.USER_ID = 2
GROUP BY TEST_TYPE

Upvotes: 2

Related Questions