Hi all This was obviously a tricky question since I got no response - so I'll repost it below. BTW the tables aren't set in stone - they could be redesigned to make this work.
I need to display teaching resources assigned to a course. Each course should show: All resource names, and the total number of each resource booked on that date. Right now there are three tables: resources (resource_id, description, total_available) resources allocated: (allocation_id, resource_id, event_id) and events with event_id and event_date So far the closest I've got is <cfquery name="GetRecord" datasource="#variables.database#"> SELECT DISTINCT SUM(Resource_Allocation.Resource_Id)AS Booked,Resources.Total_Available, Resources.Description, Events.Event_Date FROM Events,Resources,Resource_Allocation WHERE Resource_Allocation.Resource_Id = Resources.Resource_Id AND Resource_Allocation.Event_Id = Events.Event_Id AND Events.Event_Date = #variables.EventDate# GROUP BY Resources.Description, Resources.Total_Available,Events.Event_Date </cfquery> But this only shows the resources booked for that day, not the whole list of resources I'm grateful if anyone can help Crispin Ryan ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
