Count multiple tables + same row name

marxx · 09-26-2007, 08:21 AM

Hi!

How to count from multiple tables where all has same rowname?

table: commentsimage
rowname: poster

table: commentsprofile
rowname: poster

table: commentscafe
rowname: poster

I have tryed something like this which doesn't work tho..

PHP Code:


			
$count = mysql_query("SELECT COUNT(poster) FROM commensimage, commentsprofile, commentscafe WHERE poster = $poster") or die(mysql_error());

Thanks for all help!

Karl · 09-26-2007, 11:44 AM

Hi there, try the following code

PHP Code:


			
 $count = mysql_query("    SELECT 
                COUNT(DISTINCT commensimage.poster) as commensimage_count,  
                COUNT(DISTINCT commentsprofile.poster) as commentsprofile_count, 
                COUNT(DISTINCT commentscafe.poster) as commentscafe_count 
            FROM 
                commensimage, commentsprofile, commentscafe 
            WHERE 
                poster = $poster;") or die(mysql_error());

THis should return 3 seperate counts. If you wanted to add them all together and return the total, you can do something like this:

PHP Code:


			
SELECT 
                COUNT(DISTINCT commensimage.poster) + 
                COUNT(DISTINCT commentsprofile.poster) +
                COUNT(DISTINCT commentscafe.poster) as total_count 
            FROM