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]
