FInd missing row based on Column data -MySQL

Question

I am currently working on a.Net web form solution which generates a brief service report for admins to monitor the services done by technicians.As of now , i am having some trouble in coming up with an efficient SQL (for MySQl) which return data rows along with the missing rows based on the SertvicePrtNum , which is in order. For Example :- This is my raw data in the table :-

Id    ServiceRptNum  Customer_ID  Date of Service 
----  -------------  -----------  ---------------
1      1001              3        09/10/1997
2      1003              8        10/06/2005 
3      1005              1        21/02/2003
4      1007              7        1/06/2011
5      1010              4        4/11/2012
6      1002              2        16/01/2003

Here the ServiceRptNum , 1004 is missing in the table. So i want the db to return the result as : -

Id    ServiceRptNum  Customer_ID  Date of Service 
----  -------------  -----------  ---------------
1      1001              3        09/10/1997
2      1002              2        16/01/2003
3      1003              8        10/06/2005 
-      1004              -            - 
4      1005              1        21/02/2003
-      1006              -            -
5      1007              7        1/06/2011
-      1008              -            - 
-      1009              -            -
6      1010              4        4/11/2012

Here , the sql additionally generated 1004,1006,1008,1009 since it cannot find those records.

Please note that the Id is automatically generated (auto_increment)while insert of the data.But the Service ReportNum is not , this is to enable the admin to add the service report later on with the manually generated report Num (report num in the hardcopy of the company Servicebook).

Answer 1

Look here for ideas on how to generate a group of continuous integers, then select from that left outer join your table. You should get a row for every number but all the values will be null for the missing numbers.

Answer 2

You basically need to invent a constant, sequential stream of numbers and then left join your real data to them. For this method to work, you need a table with enough rows in it to generate a counter big enough:

select ID, 1000+n as servicerptnum, customer_id, `Date of Service` from
(
 SELECT  @curRow := @curRow + 1 AS n
 FROM    somebigtable 
 JOIN    (SELECT @curRow := 0) r
 WHERE   @curRow<100
) numbergen
LEFT JOIN
tablewithmissingservicerptnum
ON
  servicerptnum = 1000+n

You need to alter some things in the code above because you never told us the name of your table with missing rptnums. You also need to utilise another table in your database with more rows than this table because the way this method works is to count the rows in the bigger table, giving each a number. If you don't have any table bigger than this one, we can probably get enough rows by cross joining a smaller table to itself or by using this table. Replace somebigtable with thistable CROSS JOIN thistable where this table is the name of the table with missing servicerptnums

If you want just the rows that are missing, add a WHERE servicerptnum is null to the end of the sql

Edit, I see you've changed your numbering from:

1001
1002
...
1009
10010

To:

1009
1010

The join condition used to be servicerptnum = concat('100', cast(n as varchar)), it is now servicerptnum = 1000+n..