* Robert Kilroy
> I've been trying to work out a query for this project
> and I'm finally at the end of my rope. Basically, I
> have a ticket sales app. There is a table that has
> row_num and seat_num and seat_status. What I need to
> do is retrieve X number of seats that are sequential.
> For example, if someone requests 4 seats, and seats 1
> and 2 are sold, he would get seats 3, 4, 5, 6. Or if
> seats 5, 6 are sold he would get 7, 8, 9, 10.
Try a grouped self join:
SELECT t1.row_num,t1.seat_num,COUNT(t2.seat_num)+1 AS "seats"
FROM theater t1
LEFT JOIN theater t2 ON
t2.row_num = t1.row_num AND
t1.seat_num < t2.seat_num AND
t2.seat_num < t1.seat_num + 4 AND
t2.seat_status="free"
WHERE
t1.seat_status="free"
GROUP BY
t1.row_num,t1.seat_num
HAVING
seats = 4;
This example finds 4 seats, both occurences of the digit "4" in the above
query needs to be dynamic, and changed to whatever number of seats you want
to find. You can also find seats "in the back" or "in the front" by adding a
check in the WHERE clause on t1.row_num larger or smaller than whatever is
the middle seat row in the theatre.
Explanation of the query: First we read all free seats
(t1.seat_status="free"), then we join to the same table, finding all free
seats on the same row (t2.row_num = t1.row_num), with a higher seat number
(t1.seat_num < t2.seat_num), but smaller than the current seat from t1 + 4
(t2.seat_num < t1.seat_num + 4 ), because we want to find 4 seats.
The counter is one off, because we don't count the first seat from t1, that
is why you need to add 1 to the COUNT().
Then we use GROUP BY to get one result row per seat, and finally HAVING to
select only the free seats with 3 free seats next to it.
HTH,
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
