[prev in list] [next in list] [prev in thread] [next in thread] 

List:       php-db
Subject:    [PHP-DB] Re:Subject: multiple tables and amounts
From:       Neil Smth <php () comatose ! freeserve ! co ! uk>
Date:       2004-01-29 18:53:31
Message-ID: 5.2.0.9.0.20040129184551.00a84588 () pop ! freeserve ! net
[Download RAW message or body]

Would this work better ?

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@abendstille.at>
>To: PHP-DB <php-db@lists.php.net>
>Message-ID: <BC3E3AB5.5E04%grillen@abendstille.at>
>Mime-version: 1.0
>Content-type: text/plain; charset="US-ASCII"
>Content-transfer-encoding: 7bit
>Reply-To: grillen@abendstille.at
>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

[prev in list] [next in list] [prev in thread] [next in thread]