COUNT() IN A 3 TABLE JOIN

[virtual_odin]

I am trying to record separately views and clicks for a set of items.  I have started with three tables 1: the items; 2: the time of the view, reference by the id in table 1; and 3: the time of the click, also referenced by the id in table 1.

So I thought this would give me the answer, but it gives the same number of views and clicks.  Where have I gone wrong please?  I did wonder about a tale of views and click with a "type" field, but that doesn't feel as neat.

[code]
SELECT t1.*,
COUNT(t2.est_id) AS clicks,
COUNT(t3.est_id) AS impressions

FROM t1
LEFT JOIN t2 ON ( t1.id = t2.est_id )
LEFT JOIN t3 ON ( t1.id = t3.est_id )

GROUP BY t1.id
ORDER BY NULL
[/code]

As always, you assistance would be greatly appreciated.

[fenway]

Well, those are both equal to t1.id, and since they both see the same number of records....

[virtual_odin]

But there are different numbers of rows with t1.id in tables t2 and t3.  What I get is count(t2.est_id) * count(t3.est_id) or count(t3.est_id) if count(t2.est_id) is nil.  I must be close!

[fenway]

The problem is that you can't just count the records from half of the join.

[virtual_odin]

What would you suggest instead?  Two separate queries?

[fenway]

Probably, or some fancy subselects, not sure which one would be faster in your case.

[virtual_odin]

This now works.  Thanks for your help fenway.

[code]
$query1 = "SELECT
t1.*,
COUNT(t3.est_id) AS clicks
FROM t1
LEFT JOIN t3 ON ( t1.id = t3.est_id )
GROUP BY t1.id
ORDER BY NULL";

$query2 = "SELECT
COUNT(t2.est_id) AS impressions
FROM t1
LEFT JOIN t2 ON ( t1.id = t2.est_id )
GROUP BY t1.id
ORDER BY NULL";

$result1 = mysql_query($query1);
$result2 = mysql_query($query2);
$numresults = mysql_num_rows($result1);
for ($i=0; $i <$numresults; $i++) {
$row1 = mysql_fetch_assoc($result1);
$row2 = mysql_fetch_assoc($result2);
$row1['impressions'] = $row2['impressions'];
// etc
}
[/code]

I did it this way to end up with a single array to work with for the next part of the script.

[fenway]

Alternatively, you could have pulled that into a subquery, depending on the rest of your script.

[virtual_odin]

I'm running on 3.23.49, so I think this is one for the future...  Thanks anyway

[fenway]

Then you upgrade to 4.1 sooner rather than later ;-)

[mindloop]

I have ran into the very same problem. what would be the subquery sollution, as i cannot split my query into two separate queries.
I have described my similar problem here : [url=http://www.phpfreaks.com/forums/index.php/topic,124850.0.html]http://www.phpfreaks.com/forums/index.php/topic,124850.0.html[/url]