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]
