Sounds like you are doing the wrong kind of join. When you want all the records from one table (in your case resources) and only those record s from the other table that match (in your case events) that calls for INNER , OUTER, LEFT and/or RIGHT joins.
Here is an example from Access that should select the type of data yo u want. I didn't have any sample data so I'm guessing a little bit. You shou ld be able to add your necessary SUM and GROUP BY elements to this query. SELECT resources_allocation.resource_id, resources.total_available, resources.description, events.eventDate FROM (resources LEFT JOIN resources_allocation ON resources.resource_id= resources_allocation.resource_id) INNER JOIN events ON resources_allocation.event_id = events.event_I D; If you need a bit more help. Feel free to contact me off list. If I had a sampling of the data your using I could refine the query more. Ian Skinner Web Developer www.ilsweb.com ----- Original Message ----- From: "Crispin Ryan" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, February 28, 2002 2:37 AM Subject: Problem query > 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.Eve nt_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 whol e 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/[email protected]/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
