From: Stuart Kidd
To: CF-Talk
Sent: Wednesday, January 21, 2004 7:34 PM
Subject: RE: Database confusion.
Thanks Matthew, I'll give that a try. as soon as I wake up as it's now
1.34am! :-)
-----Original Message-----
From: Matthew Walker [mailto:[EMAIL PROTECTED]
Sent: 22 January 2004 01:17
To: CF-Talk
Subject: RE: Database confusion.
Something like this:
SELECT PropertyData.PropertyDataID,
PropertyData.PropertyReference,
PropertyData.PropertyNumberName,
PropertyData.PropertyStreetName,
PropertyData.PropertyPostcodeZip,
PropertyData.PropertyDateAdded,
Count(PropertyEnquiry.PropertyEnquiryID) AS countEnquiries
FROM PropertyData LEFT JOIN PropertyEnquiry
ON PropertyData.PropertyDataID =
PropertyEnquiry.PropertyDataID
WHERE PropertyAgentID = '#Client.PropertyAgentID#'
GROUP BY PropertyData.PropertyDataID,
PropertyData.PropertyReference,
PropertyData.PropertyNumberName,
PropertyData.PropertyStreetName,
PropertyData.PropertyPostcodeZip,
PropertyData.PropertyDateAdded
The left join joins the tables, but shows all records from the data
table
even if there are no associated records in the enquiry table.
-----Original Message-----
From: Stuart Kidd [mailto:[EMAIL PROTECTED]
Sent: Thursday, 22 January 2004 12:37 p.m.
To: CF-Talk
Subject: Database confusion.
Hi,
I've got two tables in a database.
<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
So all up I'll have on screen to view:
PropertyReference - PropertyStreetName - PropertyPostcodeZip -
PropertyDateAdded - Number of rows that PropertyDataID occurs WHERE is
equal to Table1 (a count of how many times it exists).
The first 4 fields I can pull out of the first table no problem, but the
last field (5) from Table2 I can't seem to get my head around.
I thought maybe first list all of the properties in table1 WHERE
PropertyAgentID = #Client.PropertyID# and then within that do another
pull from the database but from table2??
Is there an easier option? I am very confused (you probably can tell).
Reference|Address|Post Code|Date Added|Enquiries
j4dk3|28 Boundary St|W1F 3LB|15/01/04|2
keid5|15 Atwar Road|E1 1HD|18/01/04|3
To drag this explaination on a bit more I will put my code.
<tr>
<td align="left">Reference</td>
<td align="left">Address</td>
<td align="left">Post code</td>
<td align="left">Date inserted </td>
<td align="left">Number of Phone Enquiries</td>
</tr>
<CFQUERY NAME="DisplayPropertyList" datasource="020">
SELECT PropertyDataID, PropertyReference, PropertyNumberName,
PropertyStreetName, PropertyPostcodeZip, PropertyDateAdded
FROM PropertyData
WHERE PropertyAgentID = '#Client.PropertyAgentID#'
</CFQUERY>
<CFQUERY NAME="DisplayEnquiryInfo" datasource="020">
SELECT PropertyDataID
FROM PropertyEnquiry
WHERE PropertyDataID = '#DisplayPropertyList.PropertyDataID#'
</CFQUERY>
<cfoutput query="DisplayPropertyList">
<tr>
<td align="left">#PropertyReference#</td>
<td align="left">#PropertyNumberName# #PropertyStreetName#</td>
<td align="left">#PropertyPostcodeZip#</td>
<td align="left">#PropertyDateAdded#</td>
<td align="left">Number of Enquiries</td>
</tr>
</cfoutput>
Any help would be appreciated... And thanks for reading this far.
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
