Combining subquery results into one field

Hello there

I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms. I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking. Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query. The table structures are as follow:

bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C. I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

thanks

I would look at returning two results to a dataset then creating a relationship between them. Displaying the results is pretty easy using nested repeaters:http://gridviewguy.com/ArticleDetails.aspx?articleID=185