--- Roger Baklund <[EMAIL PROTECTED]> wrote: > * 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] >
__________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
