SELECT comments.id, comments.phid, COUNT(comments.id) AS comments, COUNT(ratings.id) AS ratings FROM photos LEFT OUTER JOIN comments ON photos.id=comments.phid LEFT OUTER JOIN ratings ON photos.id=ratings.phid WHERE photos.id=1 GROUP BY comments.id, comments.phid
(You cannot GROUP BY count values btw as you probably realise) I'm not clear where creating a temporary table gets you ;-)
Cheers - Neil.
At 08:39 29/01/2004 +0000, you wrote:
Dave Date: Thu, 29 Jan 2004 04:21:09 +0100 From: ma <grillen@xxxxxxxxxxxxxx> To: PHP-DB <php-db@xxxxxxxxxxxxx> Message-ID: <BC3E3AB5.5E04%grillen@xxxxxxxxxxxxxx> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Reply-To: grillen@xxxxxxxxxxxxxx Subject: multiple tables and amounts
hi!
i have a weired problem:
there are 3 tables: photos (id, img) comments (id, phid, text) ratings (id, phid, text)
now i tried to create a query returning the amount of ratings and the amount of comments for all images in the photo-table.
i tried it with multiple joins, but without success... but i ended up using some CREATE TEMPORARY TABLE syntax - is there a way to do it one-query?
CREATE TEMPORARY TABLE comments_temp SELECT comments.id, comments.phid, COUNT(comments.id) AS ratings FROM comments LEFT JOIN ratings ON ratings.phid=comments.phid WHERE comments.phid=1 GROUP BY comments.phid, comments.id;
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
