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.
If you have the luxury of being able to change this, consider replacing it with a table with row_num, low_seat_num, n_seats, and seat_group_status. In other words, store information on consecutive groups of seats instead of on individual seats. This table will be significantly smaller than your original table, yet it will make queries such as the one you describe practical.
The down side is that as I described it, there's redundant data in it, which can lead to inconsistency if a program gets careless: what if there are two overlapping ranges of seats described in the table? You could avoid that problem by not storing high_seat_num instead of low_seat_num and n_seats, and by having the range automatically start with the previous record found (or '1', if there is no previous record for the row of seats). In other words, if there were two records for a row of seats and one had a high_seat_num of 6 and the other a high_seat_num of 15, the first one would be considered to describe a range of seats from 1 to 6 and the second would be seats from 7 to 15. Although this structure would be stabler, it would make querying more difficult.
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.
Here's a sample query the first way, with both low_seat_num and n_seats represented. :seats_needed will be a variable indicating how many seats are requested. To keep it simple, in these queries I'm going to find *all* seat ranges suitable instead of the one in the lowest row.
SELECT * FROM row_range WHERE n_seats >= :seats_needed AND seat_status = AVAILABLE;
Note that this query is *very* efficient if there's a compound index defined on seat_status followed by n_seats.
Things are much more complex the second way, where we have high_seat_num. I will assume that there's a dummy record for each row with high_seat_num = 0 and seat_group_status = DUMMY for simplicity. Here's one way of doing it.
SELECT r2.row_num, max (r1.high_seat_num + 1) AS low_seat_num, r2.high_seat_num, r2.seat_group_status FROM row_range r1, row_range r2 WHERE r1.row_num = r2.row_num AND r1.high_seat_num < r2.high_seat_num AND r2.seat_group_status = AVAILABLE GROUP BY r2.row_num, r2.high_seat_num, r2.seat_group_status HAVING r2.high_seat_num + 1 - r1.high_seat_num >= :seats_needed;
Here's a way of doing it with your current structure. It requires a subquery, though, so it won't work in all versions of MySQL.
SELECT r.row_num, r.seat_num AS low_seat_num, r.seat_num + :seats_needed
AS high_seat_num, r.seat_status
FROM row_info r
WHERE r.seat_status = AVAILABLE AND
not exists (SELECT * from row_info r1
WHERE r.row_num = r1.row_num AND r1.seat_num <
r.seat_num + :seats_needed AND r.row_status <> AVAILABLE);And, finally, here's a way of doing it with your current structure without subqueries, but requiring multiple SQL statements and a temporary table.
/* ranges will contain all candidate ranges of seats, where the first and last are AVAILABLE, but there might be unavailable seats in between */ CREATE TEMPORARY TABLE ranges SELECT r1.* FROM row_info r1, row_info r2 WHERE r1.row_num = r2.row_num AND r1.seat_num + :seats_needed - 1 = r2.seat_num AND r1.seat_status = AVAILABLE AND r2.seat_status = AVAILABLE;
/* good_ranges uses a left join to find rows in "ranges" without
non-AVAILABLE seats in the range */
CREATE TEMPORARY TABLE good_ranges
SELECT r.*
FROM ranges r LEFT JOIN row_info bad ON r.row_num = bad.row_num
AND (bad.seat_num BETWEEN r.seat_num + 1 AND r.seat_num
+ :seats_needed -2)
AND bad.seat_status <> AVAILABLE
WHERE bad.row_num IS NULL;SELECT * FROM good_ranges;
DROP ranges, good_ranges;
Warning: None of the above queries has been tested!
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
