RE: Left Outer Join Question
Kamie, If you do have to break this out, you might want to concider using UNION ALL This was you could encapsulate your (big) query in either a view or a single Cold Fusion query. It works like this: SELECT count(*) AS num FROM a WHERE a.id = x UNION ALL Select count(*) AS num FROM b WHERE b.id = x -Original Message- From: Kamie Curfman [mailto:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 9:55 AM To: CF-Talk Subject: Re: Left Outer Join Question Thank you very much for the code, Glenn, and the thought that went behind it. Unfortunately, the SUM approach is returning the exact same multiplier effect as COUNT was. Looks like I will have to break this out into individual queries. At least I learned something from your code (I had never used SUM before in the way you have below). Thanks! Kamie Glenn Olsen wrote: > 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. > > > > > >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= > value="#attributes.report_company#" > > cfsqltype="cf_sql_integer"> > >GROUP BY company > > > > > > 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] > > > > > > ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
Re: Left Outer Join Question
Thank you very much for the code, Glenn, and the thought that went behind it. Unfortunately, the SUM approach is returning the exact same multiplier effect as COUNT was. Looks like I will have to break this out into individual queries. At least I learned something from your code (I had never used SUM before in the way you have below). Thanks! Kamie Glenn Olsen wrote: > 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. > > > > > >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= > value="#attributes.report_company#" > > cfsqltype="cf_sql_integer"> > >GROUP BY company > > > > > > 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] > > > > > > ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
RE: Left Outer Join Question
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. > > >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= value="#attributes.report_company#" > cfsqltype="cf_sql_integer"> >GROUP BY company > > > 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
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. > > >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= value="#attributes.report_company#" > cfsqltype="cf_sql_integer"> >GROUP BY company > > > 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
RE: Left Outer Join Question
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. 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= GROUP BY company 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
Re: Left Outer Join Question
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. > > 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= value="#attributes.report_company#" > cfsqltype="cf_sql_integer"> >GROUP BY company > > > 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
