CODEWITHSUNDEEP
mysqlsqlif-statement
hmd
hmd

Reputation: 970

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.

Upvotes: 0

Views: 41

Answers (3)

Teja
Teja

Reputation: 13544

SELECT A.IP 
  FROM  IP_Addresses A, 
       ( SELECT min(ID) mid
           FROM IP_ADDRESES 
          WHERE in_use= 0
        ) B
WHERE A.id = B.mid;

Upvotes: 0

Andreas
Andreas

Reputation: 5113

Building on xQbert's answer:

select  IP
from   ( select case when id >
        ( select min( ID )
          from    IP_ADDRESES
          where   in_use = 1 ) 
        then ID - ( select max( ID ) from IP_ADDRESES )
        else id 
        end case
    from IP_ADDRESES as deflated_id,
    ip )
order by deflated_id asc 
top 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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

Upvotes: 1

Related Questions