multiple tables and amounts
| From: | ma | Date: | Thu, 29 Jan 2004 03:21:09 +0000 |
| Subject: | multiple tables and amounts | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-32689@lists.php.net to get a copy of this message | ||
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;
SELECT photos.*,
COUNT(photos.id) AS comments,
comments_temp.ratings
FROM photos
LEFT JOIN comments_temp ON comments_temp.phid = photos.id
WHERE gid=1
GROUP BY photos.id;
DROP TABLE comments_temp;
as said i would like to do it one-query, because it does not seem to me
being a simple and beautiful solution??
help would be warmly appretiated... thx alot
- mathew
# life would be easier if i knew the source code...