> 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 <lists@stripped>
> 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.
