Finding/searching for missing values in MySQL

I am using MySQL and have a table c开发者_Python百科alled sales. Its primary key is sales_id.

-------------------------------------
sales_id | invoice_id | 
-------------------------------------

1  |  147 
2  |  148
3  |  150

For sales_id 3 the invoice_id is supposed to be 149. I want to know which numbers are missing from invoice_id. I start invoice_id at 147 and end invoice_id at 4497. The invoice_id had no relation with sales_id.

Is it possible to know which numbers are missing from invoice_id, using a query?

---

I presume that you have a table of invoice - INVOICES. You may try:

 SELECT invoice_id FROM INVOICES invoice
 WHERE NOT EXISTS (SELECT * FROM SALES s WHERE invoice.invoice_id = s.invoice_id)

EDIT: If you don't have the INVOICES table, you may need to take all the invoices out before checking if there's any gap.

SELECT DISTINCT invoice_id FROM SALES ORDER BY invoice_id ASC SELECT MAX(invoice_id) FROM SALES

then, by php:

for ($i = 1; $i < $max_invoice_id; $i++)
{
  if (!in_array($i, $all_invoice_id))
  {
    $gapId[] = $i;
  }
}

---

If you're just trying to find numbers out of the sequence in the example you've provided you could use something like this:

SELECT *
FROM  `sales` 
WHERE (
`invoice_id` - 147 - `sales_id`
) = 0

If you can provide a bigger sample set we might be able to tool a better answer for you.

---

set @suggest_invoice:=147;

select 
  sales_id, 
  invoice_id,
  @suggest_invoice:=@suggest_invoice+1,
  if(invoice_id=@suggest_invoice, 0, 1) as missing_invoice
from sales
order by sales_id;

I guess you almost getting all the records except the first two with missing_invoice=1

---

As you are searching for invoice_id then make sure you have an index on that column. otherwise queries will be slow.

You can try the following code

$inv_ids=range(147,4497);
$str_inv_ids=implode(",",$inv_ids);
$query="SELECT DISTINCT invoice_id FROM sales WHERE invoice_id IN ($str_inv_ids)";
$result=mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
    $inv_ids_db[]=$row['invoice_id'];
}
// It gives invoice Ids that are in $inv_ids but not in $inv_ids_db
$missing= array_diff($inv_ids,$inv_ids_db); 

print_r($missing);

---

You can find gaps in a MySQL sequence using this query:

SELECT invoice_id+1
   FROM sales s
   WHERE NOT EXISTS (
       SELECT NULL
       FROM sales t
       WHERE s.invoice_id = t.invoice_id+1
   ) 
   HAVING `invoice_id+1` < (SELECT MAX(invoice_id) FROM sales)
   ORDER BY invoice_id

This will return all invoice_id missing from the sequence, regardless of sales_id.