Find Missing numbers in SQL query

Question

I need to find the missing numbers between 0 and 16.

My table is like this:

CarId    FromCity_Id    ToCity_Id    Ran_Date    RunId
1001        0              2        01-08-2013     1
1001        5              9        02-08-2013     2
1001        11             16       03-08-2013     3
1002        0              11       02-08-2013     4
1002        11             16       08-08-2013     5

I need to find out:

In past three months from now(), between which cities the car has not ran.

For example, in the above records:

• Car 1001 not ran between 02-05 & 09-11
• Car 1002 has run fully (ie between 0-11 and 11-16)

Over all is that, I need to generate a query which shows the section between which the car has not run in past 3 months with showing the last run date.

How to make such an query please. If any Stored Procedure please advise.

Answer 1

God help me. This uses a doubly-correlated subquery, a table that might not exist in your system, and too much caffeine. But hey, it works.

Right, here goes.

SELECT CarId, GROUP_CONCAT(DISTINCT missing) missing
FROM MyTable r, 
   (SELECT @a := @a + 1 missing 
    FROM mysql.help_relation, (SELECT @a := -1) t 
    WHERE @a < 16 ) y
WHERE NOT EXISTS 
   (SELECT r.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id
       AND r.carid = m.carid)
GROUP BY CarID;

Produces (changing the first row for CarID 1002 to 0-9 to open up 10 and give us better test data):

+-------+---------+
| CarId | missing |
+-------+---------+
|  1001 | 3,4,10  |
|  1002 | 10      |
+-------+---------+
2 rows in set (0.00 sec)

And how does it all work?

Firstly... The inner query gives us a list of numbers from 0 to 16:

   (SELECT @a := @a + 1 missing 
    FROM mysql.help_relation, (SELECT @a := -1) t 
    WHERE @a < 16 ) y

It does that by starting at -1, and then displaying the result of adding 1 to that number for each row in some sacrificial table. I'm using mysql.help_relation because it's got over a thousand rows and most basic systems have it. YMMV.

Then we cross join that with MyTable:

SELECT CarId, ...
FROM MyTable r, 
   (...) y

This gives us every possible combination of rows, so we have each CarId and To/From IDs mixed with every number from 1-16.

Filtering... This is where it gets interesting. We need to find rows that don't match the numbers, and we need to do so per CarID. This sort of thing would do it (as long as y.missing exists, which it will when we correlate the subquery):

    SELECT m.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id 
       AND m.CarID = 1001;

Remember: y.missing is set to a number between 1-16, cross-joined with the rows in MyTable. This gives us a list of all numbers from 1-16 where CarID 1001 is busy. We can invert that set with a NOT EXISTS, and while we're at it, correlate (again) with CarId so we can get all such IDs.

Then it's an easy matter of filtering the rows that don't fit:

SELECT CarId, ...
FROM MyTable r, 
   (...) y
WHERE NOT EXISTS 
   (SELECT r.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id 
       AND r.carid = m.carid)

Output To give a sensible result (attempt 1), we could then get distinct combinations. Here's that version:

SELECT DISTINCT CarId, missing
FROM MyTable r, 
   (SELECT @a := @a + 1 missing 
    FROM mysql.help_relation, (SELECT @a := -1) t 
    WHERE @a < 16 ) y
WHERE NOT EXISTS 
   (SELECT r.CarID FROM MyTable m 
    WHERE y.missing BETWEEN FromCity_Id AND ToCity_Id 
       AND r.carid = m.carid);

This gives:

+-------+---------+
| CarId | missing |
+-------+---------+
|  1001 |       3 |
|  1001 |       4 |
|  1001 |      10 |
|  1002 |      10 |
+-------+---------+
4 rows in set (0.01 sec)

The simple addition of a GROUP BY and a GROUP CONCAT gives the pretty result you get at the top of this answer.

I apologise for the inconvenience.