Choosing Consecutive Seats by Seatrow

SELECT TOP N seats.*, eventtickets.eticketprice from seats 
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE ((Seats.seatType ='BOXSEAT') 
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
ORDER BY convert (int, seats.seatSection), seats.seatrow ASC

My query above generates the best available # of N seats, but it will fill up one section and then the rest of that order is put in the next section. I need it to find a new section that has all N seats available. IF a seat is in TicketSales table it is not available. If salecategory is Null then that means ticket has not been SOLD/BLOCK/HOLD so its Available.

It is obviously limited to 6 seats together, but I do have one section which has over 30 seats in one row.

Section 1: 
Row 1: 1-2-3
Row 2: 4-5-6

Section 2:
Row 1: 1-2-3
Row 2: 4-5-6

etc.

I am at my wits end. Been staring at this for 2 days straight and my mind is mush. I am pretty weak at SQL, amazing I got this far above. I think I have gotten close, but no enchilada.

See pics of the 2 main tables attached. I just need the seatprice from eventtickets table so it's not that important.

I thank you for any help you can give me on this.

If you're happy with what you have already, try starting with a set of the distinct sections, cross joining on that, and then filtering your subquery to only that section. That way, it's like considering each section as if it's its own event.

But... why not consider looking at all the seats and finding blocks where the (N-1)th next one is the actual right number away:

SELECT * FROM
(SELECT seats.*, eventtickets.eticketprice,
    LEAD(seats.SeatID,@N-1) OVER (PARTITION BY seats.seatSection ORDER BY seats.SeatID) AS SeatNAway
from seats 
INNER JOIN eventtickets ON eventtickets.etickettype = seats.seattype 
WHERE ((Seats.seatType ='BOXSEAT') 
AND seatID not in (select seatID from ticketsales WHERE eventID=6))
) s
WHERE s.SeatId = s.SeatNAway - (@N-1)
ORDER BY convert (int, s.seatSection), s.seatrow ASC

See how you go with this maybe... check that you understand the inner subquery, of course.

StackExchange Database Administrators Q#186872, answer score: 5