Kamie,
You'll need four separate queries for this.
When you JOIN(c.company_id=srl.company_id) and SELECT
Count(srl.company_id), you get a count of all the srl
matches with a certain company id.
When you JOIN(c.company_id=srl.company_id) AND JOIN
(c.company_id=el.company_id), you get all the srl
matches, and then FOR EACH OF THOSE MATCHES, you also
get all el matches with the company id. Hence there's
a multiplier effect.
--- Kamie Curfman <[EMAIL PROTECTED]> wrote:
> 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
