SQL query: finding a gap in a primary key

Question

Hi there

How can I write a single select statement that does the following:

I have an integer column in my table and i want to find the minimum available (non-used) value in that column where the value is below 1000 and also where the value does not exist in TableB Column1

Thanks

Answer 1

Similar to LukeH's answer but it does what you asked for:

SELECT MIN(a.your_column) - 1 AS answer
FROM your_table AS a
LEFT JOIN your_table AS a2
        ON a2.your_column = a.your_column - 1
LEFT JOIN tableB AS b
        ON a.your_column = b.column1
WHERE a.your_column < 1000
    AND b.column1 IS NULL
    AND a2.your_column IS NULL

---

Edit:

UNION
SELECT MIN(a.your_column) + 1 AS answer
FROM your_table AS a
LEFT JOIN your_table AS a2
        ON a2.your_column = a.your_column + 1
LEFT JOIN tableB AS b
        ON a.your_column = b.column1
WHERE a.your_column < 1000
    AND b.column1 IS NULL
    AND a2.your_column IS NULL

And pick the minumum of the two values.

It still needs checking if the value 1 is available, but if you have a gap between A and B it should find A+1 and B-1 now and you could pick the smallest. Obviously A+1 is the smallest so you can just use the second part...

Answer 2

This results in 7, which I believe would be the correct answer given your criteria

CREATE TABLE #TableA (Value INT)
INSERT #TableA (Value) VALUES (1)
INSERT #TableA (Value) VALUES (2)
INSERT #TableA (Value) VALUES (3)
INSERT #TableA (Value) VALUES (5)
INSERT #TableA (Value) VALUES (6)
INSERT #TableA (Value) VALUES (8)

CREATE TABLE #TableB (Value INT)
INSERT #TableB (Value) VALUES (4)

SELECT    MIN(A1.Value) + 1
FROM      #TableA A1
LEFT JOIN #TableA A2 ON A2.Value = A1.Value + 1
LEFT JOIN #TableB B1 ON B1.Value = A1.Value + 1
WHERE     A2.Value IS NULL
AND       B1.Value IS NULL
AND       A1.Value < 1000

DROP TABLE #TableA
DROP TABLE #TableB