I know this is more SQL than CF, but this list is so responsive...
I'm trying to do a query that counts how many times a particular company
showed up in the search results log table, how many times its URL was
clicked on, etc. So, I'm left outer joining all the log tables, on the
chance that the company wasn't returned in any of the searches, its URL
was never clicked on, etc. I thought the below logic was sound, but
instead of getting "14" for search_count, "4" for enhanced_count, "1"
for click_through_count, and "1" for contact_me_count, I'm getting "56"
for all. In other words, 14*4*1*1. Why is this happening? Why aren't
I getting separate counts for each?
Here is the query. Thank you so much in advance.
<cfquery datasource="#request.datasource#" name="get_company">
SELECT c.company, COUNT(srl.company_id) AS search_count,
COUNT(el.company_id) AS enhanced_count, COUNT(ctl.company_id) AS
click_through_count, COUNT(cfl.company_id) AS contact_me_count
FROM dbo.Companies c
LEFT OUTER JOIN dbo.Search_Results_Log srl ON
(c.company_id=srl.company_id)
LEFT OUTER JOIN dbo.Enhanced_Log el ON
(c.company_id=el.company_id)
LEFT OUTER JOIN dbo.Click_Through_Log ctl ON
(c.company_id=ctl.company_id)
LEFT OUTER JOIN dbo.Contact_Form_Log cfl ON
(c.company_id=cfl.company_id)
WHERE c.company_id=<cfqueryparam value="#attributes.report_company#"
cfsqltype="cf_sql_integer">
GROUP BY company
</cfquery>
Kamie
--
Kamie B. Curfman
Matrix Group International, Inc.
801 N. Fairfax Street, Suite 205
Alexandria, VA 22314
Phone: (703) 838-9777 x3014
Fax: (703) 838-9799
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
