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
