Lowest unused ID from two combined tables

Question

I'm trying to write a SQL query to find the lowest available/unused ID from a column named internal that exists in two separate tables:

machines
machines_ignore

Data is processed from an external source, and we want to fetch data from all machines that are not in the machines_ignore table. The ignore table is just a manual table set up by us when we identify machines we don't want to analyze.

I've found scripts that work on a single table (like only the machines table), but as soon as I try to get it working when combining two tables.

Example

Table 1 (machines)

id	internal
1	1
2	2
3	3
4	5
5	6

Table 2 (machines_ignore)

internal
4
7
8
9
12

Expected result

Based on the example above, this query should output 10, 11, 13 etc.

Any ideas?

https://stackoverflow.com/a/25719885/12181863 try taking a look at this answer? — YHStan, Jul 15 '21 at 14:00

Salman A · Accepted Answer · 2021-07-15T14:09:59.533

2

One solution is to combine the values from both tables then check if each value has next value in both tables using EXISTS:

SELECT x.internal + 1
FROM (
    SELECT internal FROM machines
    UNION
    SELECT internal FROM machines_ignore
) AS x
WHERE NOT EXISTS (
    SELECT 1 FROM machines WHERE internal = x.internal + 1
) AND NOT EXISTS (
    SELECT 1 FROM machines_ignore WHERE internal = x.internal + 1
)
LIMIT 1

edited Jul 15 '21 at 14:09

answered Jul 15 '21 at 13:45

Salman A

262,204
82
430
521

Thank you! This gives me a NULL result, which is a possibility (there are machines with internal set to NULL). How would the query need to be changed in order to ignore NULLs? – Mad Marvin Jul 15 '21 at 14:56
Where internal is not null? – Salman A Jul 15 '21 at 15:51
Yeah, my problem was where to put it. Works fine within the union subquery :) Thanks! – Mad Marvin Jul 15 '21 at 16:14
how would it work if both tables are missing 1 or 0? if it is retrieving based on values which does not have "next" value? – YHStan Jul 15 '21 at 16:27
1

@yhstan adding a simple `union select 0` should fix it – Salman A Jul 15 '21 at 16:56

Lowest unused ID from two combined tables

Example

Table 1 (machines)

Table 2 (machines_ignore)

Expected result

1 Answers1