minutes". Basically this is the idea:
This:
SELECT *
FROM table1, table2
Is an outer join. Every row in table1 will be returned with every row of
table2, so if both tables have 10 rows, the record set will have 100.
This
SELECT *
FROM table1 INNER JOIN table2
ON table1.something = table2.something
Is an inner join. Every row in table1 will be returned with every row of
table2 where the two fields match. So if table1 is properties and table2 is
enquiries, one row will return for each enquiry. If a property has no
enquiries it won't be returned at all.
SELECT *
FROM table1 LEFT JOIN table2
ON table1.something = table2.something
Is a left join. Every row in table1 will be returned with every row of
table2 where the two fields match. But if there are no matching rows in
table2, the table1 data will still be returned. The fields from table2 will
ontain NULL (just an empty string in CF). So if table1 is properties and
table2 is enquiries, one row will return for each enquiry. If a property has
no enquiries it will still be returned.
What you wanted was a variation on this. You wanted just the number of
enquiries. So to do this, we take the query above, list all the fields we
need instead of using *, add the count() field, and group by all the fields
in table1. This is called an aggregated query, using aggregate functions.
In Access look for the uppercase sigma symbol in the query builder.
-----Original Message-----
From: Stuart Kidd [mailto:[EMAIL PROTECTED]
Sent: Thursday, 22 January 2004 10:26 p.m.
To: CF-Talk
Subject: RE: Database confusion.
I put it all to the test and kazam!! It works! :-) It's going to take
me a little while to get my head around what you did though. If I read
up on joins do you think that will help?
Thanks again,
Stuart
-----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]
