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]