John Davidson

Message:

I have a table as below:

CREATE TABLE IF NOT EXISTS `room_players`

(`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY  

,`player_id` int(11) NOT NULL  

,`room_id` tinyint(1) NOT NULL  

,`dealer` tinyint(1) NOT NULL

);



INSERT INTO room_players (`player_id`, `room_id`, `dealer`) VALUES

(1,    1,    '0'),

(2,    1,    '0'),

(3,    1,    '0'),

(4,    1,    '1'),

(5,    1,    '0');

I need to get row after WHERE dealer='1' AND room_id='1'; in this case, it should return player_id = 5.

Now, when dealer = 1 for player_id = 5, then it should select the first player_id of the current room_id.

I tried:

SELECT * 

FROM room_players 

WHERE room_id='1' 

AND id IN ( SELECT ID+1 

            FROM room_players 

            WHERE room_id='1' 

            AND dealer != 1

           )

Selecting the next row works with this query but, when we got to the last player in line, it will return nothing.

I could run 2 different queries to get it work, but I would like to use an eloquent single query.

---

My Answer:
You can achieve this by using a combination of subqueries and ordering by the `id` field. Here is the query that should give you the desired result:

sql
SELECT *
FROM room_players
WHERE room_id='1'
AND id > (
SELECT id
FROM room_players
WHERE room_id='1'
AND dealer='1'
LIMIT 1
)
ORDER BY id
LIMIT 1
UNION
SELECT *
FROM room_players
WHERE room_id='1'
ORDER BY id
LIMIT 1;


This query first selects the row with `dealer='1'` and `room_id='1'`, then selects the row with the next `id` value. If there is no next row, it will return the first row of the current `room_id`.