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`.
Rate this post
5 of 5 based on 1387 votesComments