how to get lowest available unique value
<p>im trying to get the lowest available unique value but with some parameters.</p>

<p>i found <a href="https://stackoverflow.com/questions/5016907/mysql-find-smallest-unique-id-available">this</a> </p>

<p>and my statement will look like this</p>

<pre><code>SELECT MIN(t1.cloakroomnumber + 1) AS nextID FROM Wardrobe_CloakTable t1
LEFT JOIN Wardrobe_CloakTable t2
ON t1.cloakroomnumber + 1 = t2.cloakroomnumber
WHERE t1.payingcustomerID = 2 AND t1.cloakroomsection = 'B' AND t2.cloakroomnumber IS NULL
</code></pre>

<p>but this is returning something that is not correct at all.
So the question is where should i put my WHERE to the MIN argument.</p>

<p>my tables are like:</p>

<pre><code>ID (unique not important)
payingcustomerID (FK - thats why i need to get lowest for just 1 customer)
deviceID (not important)
terminalnumber (not important)
qrcode (not important)
cloakroomsection (defines if query is inserted in the same physical section)
cloakroomnumber (defines the unique number but its only unique in the
                 way that its unique in the same section AND for the same
                 paycustomerID)
</code></pre>

<p>if i have 5 different cloakroomnumbers in my database with same cloakroomsection and same payingcustomerID looking like </p>

<pre><code>1
2
3
5
6
</code></pre>

<p>i want to be able to get number 4</p>

<p>the statement in the start i got from the other post is working correctly without my try to manipulate it. but its not taking into account payingcustomer and cloakroomsection. thats what im trying to add.</p>