Kamie,
The results you are getting make perfect sense. The LEFT JOINS return NULL
values where there is no match in the right-side table. COUNT is treating
NULL as a value and incrementing your output column for each and every row.
Instead of using COUNT, redo this using SUM with CASE statements, as below.
(I haven't actually run this, so let me know if it's not exactly right.)
SELECT
c.company,
SUM(CASE WHEN srl.company_id IS NOT NULL THEN 1
ELSE 0
END) AS search_count,
SUM(CASE WHEN el.company_id IS NOT NULL THEN 1
ELSE 0
END) AS enhanced_count,
SUM(CASE WHEN ctl.company_id IS NOT NULL THEN 1
ELSE 0
END) AS click_through_count,
SUM(CASE WHEN cfl.company_id IS NOT NULL THEN 1
ELSE 0
END) 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= "#attributes.report_company#"
GROUP BY company
-----Original Message-----
From: Kamie Curfman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 13, 2001 5:15 PM
To: CF-Talk
Subject: Re: Left Outer Join Question
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
