// This is just 1 query, I have not tested it, but it may work. $get_Quote = "select ph.id, ph.img, cm.id, cm.phid, cm.txt, rt.id, rt.phid, rt.text from photos as ph left join comments as cm left join ratings as rt on ph.id = cm.phid = rt.phid where id = '$photo_id' order by ph.id"; //runs the query $get_Quote_res = mysql_query($get_Quote) or die(mysql_error()); -----Original Message----- From: ma [mailto:grillen@xxxxxxxxxxxxxx] Sent: Wednesday, January 28, 2004 10:21 PM To: PHP-DB 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; 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... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
