The LEFT JOIN didn't change anything. Thanks for the help, though. And
thanks to cfrefactoring. I was hoping to not have to break it up into
individual queries, but if that's what I have to do, that's what I have to
do. Thank you!
Kamie
Mark Stewart wrote:
> Have you tried just a LEFT JOIN?
>
> -----Original Message-----
> From: Kamie Curfman [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 13, 2001 2:19 PM
> To: CF-Talk
> Subject: Left Outer Join Question
>
> 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]
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
