Yet another LEFT JOIN question

[Ron Gilbert]

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.

Thanks, Ron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]