hmd hmd - 1 month ago 7
MySQL Question

Run SQL statement based on sql count

I want to run one SQL query based on first COUNT sql query:

SET @result = "SELECT count(*) FROM ip_addresses WHERE id > (SELECT id FROM ip_addresses WHERE in_use=1) ORDER BY id LIMIT 1"
CASE WHEN (@result > 0)
THEN SELECT * FROM ip_addresses WHERE id > (SELECT id FROM ip_addresses WHERE in_use=1) ORDER BY id LIMIT 1
ELSE SELECT * FROM ip_addresses WHERE in_use!=1 ORDER BY id LIMIT 1
END


Basically what I'm trying to do is, I want to get the next record
in_use=0
of
WHERE in_use=1
. And if the
in_use=1
record is last record of the table it should get the first record of table with
in_use=0
.
More explaination:
if
ip_addresses
table have following data

id| ip |in_use
1 | 192.168.1.5|1
2 | 89.58.1.2 |0
3 | 58.98.58.6 |0


Now it should return second record.

And if
ip_addresses
table have following data

id| ip |in_use
1 | 192.168.1.5|0
2 | 89.58.1.2 |0
3 | 58.98.58.6 |1


Now it should return first record.

Answer

You have several issues. The most important is that control flow logic is only allowed in programming blocks.

However, you can accomplish what you want with a single query. For instance:

(SELECT ia.*, 1 as priority
 FROM ip_addresses ia
 WHERE id > (SELECT id FROM ip_addresses WHERE in_use = 1)
 ORDER BY id
 LIMIT 1
)
UNION ALL
(SELECT ia.*, 2 as priority
 FROM ip_addresses ia
 WHERE in_use <> 1
 ORDER BY id
 LIMIT 1
)
ORDER BY priority
LIMIT 1
Comments