Try:
select id, clicks, count(views.adId) as views
from (select ads.id, count(clicks.adId) as clicks
from ads inner join clicks on ads.id=clicks.adId
group by id) as adsclicks
left join views on id=views.adid
group by id;
Explanation:
-- the following gives you a count of clicks for each ad
select ads.id, count(clicks.adId) as clicks
from ads inner join clicks on ads.id=clicks.adId group by id)
-- if you save it to a temporary table,
create temporary table adsclicks
select ads.id, count(clicks.adId) as clicks
from ads inner join clicks on ads.id=clicks.adId
group by id)
-- you then have a temporary table with a row for each ad and the click
counts
-- you can then left join that with the views table to get the views count,
too.
select id, clicks, count(views.adId) as views
from adsclicks
left join views on id=views.adid
group by id;
-- the query at the beginning of this message uses a subquery instead of
creating and using a temporary table.
Is the subquery better or faster? Try it and see--depends partly on whether
you have to add a column to identify individual clicks and views. On the
one hand, the count(distinct) solution looks at more rows; on the other
hand, subqueries may not get as much optimization. I'd claim that the
subquery describes better what you want, while the count(distinct) is a
kludge to avoid the subquery.
----- Original Message -----
From: "Ron Gilbert" <[EMAIL PROTECTED]>
To: "Bill Easton" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, December 06, 2004 11:05 AM
Subject: Re: Yet another LEFT JOIN question
> > If you are using 4.1 or later, you could do a subquery to count the
> > clicks,
> > then left join that with the views.
>
> I am using 4.1. I tried to do a sub-query, but never got it run. Can
> you give me a quick example? Is the sub-query a better (faster) way to
> do this?
>
> Ron
>
> On Dec 6, 2004, at 6:19 AM, Bill Easton wrote:
>
> > Ron,
> >
> > What's happening is that, when there are clicks and views for an ad,
> > you are
> > getting the number of clicks TIMES the number of views.
> >
> > A quick and dirty solution is to put a column, say id, in clicks which
> > is
> > different for each click, and similarly for views. Then, you can
> > change
> > your counts to count(distinct clicks.id) and count(distinct views.id).
> > Note
> > that, internally, MySQL will still find all of the (click, view)
> > pairs, then
> > sort them and remove duplicates--this may or may not be a problem,
> > depending
> > on usage.
> >
> > If you are using 4.1 or later, you could do a subquery to count the
> > clicks,
> > then left join that with the views.
> >
> > HTH
> >
> > Bill
> >
> >
> > From: Ron Gilbert <[EMAIL PROTECTED]>
> > Subject: Yet another LEFT JOIN question
> > Date: Sat, 4 Dec 2004 12:08:43 -0800
> >
> > I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list
> > of every time a Ad was clicked on with the Ads ID, and 'Views' is a
> > simple list of views that ad got, with the Ads ID.
> >
> > I am trying to SELECT a list of all the ads, with a count for clicks
> > and a count for views, but my LEFT JOIN is not producing what I
> > thought.
> >
> > If the click count is 0, then the view count is OK, but if not, then
> > the Click count and view count are equal, but a much too large number.
> >
> > If I just SELECT for views or clicks, then it works OK, it's when they
> > are combined that it falls apart.
> >
> > SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views
> > FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID
> > LEFT JOIN Views V ON A1.ID = V.AdID
> > group by A1.ID
> >
> > CREATE TABLE `Clicks` (
> > `AdID` int(10) NOT NULL default '0'
> > [snip]
> > )
> > CREATE TABLE `Views` (
> > `AdID` int(10) NOT NULL default '0'
> > [snip]
> > )
> > CREATE TABLE `Ads` (
> > `ID` int(10) NOT NULL default '0'
> > [snip]
> > )
> >
> > I have tried a lot of combinations for LEFT JOIN with no luck. I've
> > read all the posts on this list and they don't seem to be doing what I
> > am, or else I'm not seeing it.
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
