Why use a nested select, try this: <CFQUERY NAME="DisplayPropertyList" datasource="020"> SELECT a.PropertyDataID, a.PropertyReference, a.PropertyNumberName, a.PropertyStreetName,a.PropertyPostcodeZip, a.PropertyDateAdded, count(b.PropertyDataID) as EnquiryQty FROM PropertyData a, PropertyEnquiry b WHERE a.PropertyAgentID = '#Client.PropertyAgentID#' and a.PropertyAgentID = b.a.PropertyAgentID group by a.PropertyDataID, a.PropertyReference, a.PropertyNumberName, a.PropertyStreetName,a.PropertyPostcodeZip, a.PropertyDateAdded </CFQUERY>
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Stephen Bosworth Application Development and Integration Communication and Information Services The University of Newcastle, Australia Phone: 02 4921 6574 Fax: 02 4921 7087 Email: [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >>> [EMAIL PROTECTED] 22/01/2004 12:32:54 pm >>> Thanks for that Paul. I'm only using Access, so I probably won't be able to do that sort of stuff! :( Damn dodgy database! :) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 22 January 2004 01:15 To: CFAussie Mailing List Subject: [cfaussie] Re: Joins and stuff, maybe? On Thu, Jan 22, 2004 at 01:01:32AM -0000, Stuart Kidd wrote: > <Table1 name: PropertyData> > PropertyDataID (Primary Key) > PropertyAgentID > PropertyReference > PropertyNumberName > PropertyStreetName > PropertyPostcodeZip > PropertyDateAdded > > I have an Agent (real person) logging in (assigned by client.agentid) > who needs to pull a list of their properties. The two tables' only > common field is PropertyDataID. > > My query to pull data from table1 is straight forward. > > <CFQUERY NAME="DisplayPropertyList" datasource="020"> > SELECT > PropertyDataID, PropertyReference, PropertyNumberName, > PropertyStreetName, PropertyPostcodeZip, PropertyDateAdded > FROM > PropertyData > WHERE PropertyAgentID = '#Client.PropertyAgentID#' > </CFQUERY> > > When I list the properties on screen I would also like to display to the > agent how many people enquired about each property. The enquiries are > kept in another database, PropertyEnquiry. > > <Table2 name: PropertyEnquiry> > PropertyEnquiryID (Primary Key) > PropertyDataID > PropertyTelephoneNumber If you've got a decent database you can use sub-selects, such as: <CFQUERY NAME="DisplayPropertyList" datasource="020"> SELECT PropertyDataID, PropertyReference, PropertyNumberName, PropertyStreetName, PropertyPostcodeZip, PropertyDateAdded, (select count(*) from PropertyEnquiry where PropertyDataID = PropertyData.PropertyDataID) as EnquiryQty FROM PropertyData WHERE PropertyAgentID = '#Client.PropertyAgentID#' </CFQUERY> Oh, and you'll want to change the '#Client.PropertyAgentID#' to use <CFQUERYPARAM... like: <cfqueryparam value='#Client.PropertyAgentID#' CFSQLType='CF_SQL_<something_or_other'> Cheers Paul Haddon Technical Services Manager Formstar Print Technologies --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
