You are going to want to use a LEFT OUTER JOIN. The easiest way to get your head around how joins work is to use a query builder. Toad for Mysql is a good app for this. MS access will work as well.
On Jan 28, 2008 5:05 PM, Toby King <[EMAIL PROTECTED]> wrote: > Hi there > > I have 2 tables in A MySQL database. A room table and a room occupancy > table which stores records for a selected period of time. > > What I want to do is display all rooms (rooms 1-20) along with the status > of the room. > > Currently my query is as follows: > > select distinct tbl_rooms.roomno, tbl_jobs.bookingstatus > from tbl_rooms left join tbl_jobs > on tbl_rooms.roomid = tbl_jobs.roomid > order by roomno > > What I am getting is the following displayed: > > Rooms > > 1 1 2 2 3 4 5 6 7 8 > > Basically I have the following in my tables - I have one record for each > room in the rooms table and multiple records in the tbl_jobs table. > > Room 1 has multiple records where the booking has been completed and one > record showing that the room is currently booked. The same for room 2. Room > 3 has only been booked before and there are no current bookings for room 3. > > Room 4 has never been booked before but shows up as it is a room in the > Room table. Room 5 has a number of completed bookings and is currently > booked. > > Room 6,7 8 etc have no current bookings only completed bookings. > > Basically I just want to show 1 occurence for each room and indicate > whether it is booked or whether it is available. > > What I want to display is: > > Room Status > > 1 2 3 4 5 6 7 8 9 10 (if a room is occupied it will be highlighted as > being occupied) > > > > Thanks in advance for assistance. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297611 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
