Foreach?

Hi,

I€™m not sure whether a €œForeach€? capability is achievable in SQL; I€™ve examined joins, unions and subqueries but can€™t come up with a solution.

Scenario is 3 related tables: Location, Room, Booking (date and guest name) and what I wish to do is produce a data grid that shows every for each Location and for each room the status on a given day as follows:

Location Room Date Guest
Main 1 1/1/06 Jones
Main 2 1/1/06 Smith
Main 3 null null
Annex 1 1/1/06 Bloggs
House 1 null null


In psuedo-code what I want to do is

For a given date
Show each location
for each location show each room
for each room show guest (if present) else show null.

I can generate Location and Room OK using LEFT OUTER JOIN but when I introduce the date check it all goes pear shaped in that (obviously) the grid is not populated with nulls in place of the non-existent booking records!