Try something like this
SELECT A1.ID,
SUM(IF(ISNULL(C.AdID),0,1)) AS Clicks,
SUM(IF(ISNULL(V.AdID),0,1)) 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
-----Original Message-----
From: Ron Gilbert [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 04, 2004 2:09 PM
To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
Subject: Yet another LEFT JOIN question
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
