Find Missing numbers in SQL query

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.

select * from carstable where CarId not in 
(select distinct CarId from ranRecordTable where DATEDIFF(NOW(), Ran_Date) <= 90)

Hope this helps.

Here is the idea. Create a list of all cars and all numbers. Then, return all combinations that are not covered by the data. This is hard because there is more than one row for each car.

Here is one method:

select cars.CarId, n.n
from (select distinct CarId from t) cars cross join
     (select 0 as n union all select 1 union all select 2 union all select 3 union all
      select 4 union all select 5 union all select 6 union all select 7 union all
      select 8 union all select 9 union all select 10 union all select 11 union all
      select 12 union all select 13 union all select 14 union all select 15 union all
      select 16
     ) n
where t.ran_date >= now() - interval 90 day and
      not exists (select 1
                  from t t2
                  where t2.ran_date >= now() - interval 90 day and
                        t2.CarId = cars.CarId and
                        n.n not between t2.FromCity_id and t2.ToCity_id
                 );

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Table1
    (`CarId` int, `FromCity_Id` int, `ToCity_Id` int, `Ran_Date` datetime, `RunId` int)
;

INSERT INTO Table1
    (`CarId`, `FromCity_Id`, `ToCity_Id`, `Ran_Date`, `RunId`)
VALUES
    (1001, 0, 2, '2013-08-01 00:00:00', 1),
    (1001, 5, 9, '2013-08-02 00:00:00', 2),
    (1001, 11, 16, '2013-08-03 00:00:00', 3),
    (1002, 0, 11, '2013-08-02 00:00:00', 4),
    (1002, 11, 16, '2013-08-08 00:00:00', 5)
;

Query 1:

SELECT r1.CarId,r1.ToCity_Id as Missing_From, r2.FromCity_Id as Missing_To,
       max(t.Ran_Date) as Last_Run_Date 
FROM (
SELECT @i1:=@i1+1 AS rownum, t.*
FROM Table1 as t, (SELECT @i1:=0) as foo
ORDER BY CarId, Ran_Date) as r1
INNER JOIN (
SELECT @i2:=@i2+1 AS rownum, t.*
FROM Table1 as t, (SELECT @i2:=0) as foo
ORDER BY CarId, Ran_Date) as r2 ON r1.CarId = r2.CarId AND 
                                   r1.ToCity_Id != r2.FromCity_Id AND
                                   r2.rownum = (r1.rownum + 1)
INNER JOIN Table1 as t ON r1.CarId = t.CarId
WHERE r1.Ran_Date >= now() - interval 90 day           
GROUP BY r1.CarId, r1.ToCity_Id, r2.FromCity_Id

Results:

| CARID | MISSING_FROM | MISSING_TO |                 LAST_RUN_DATE |
|-------|--------------|------------|-------------------------------|
|  1001 |            2 |          5 | August, 03 2013 00:00:00+0000 |
|  1001 |            9 |         11 | August, 03 2013 00:00:00+0000 |