thx for the query! unfortunately it does not what i was aiming for... a result like this: id | img | comments | ratings ---+---------+----------+-------- 1 | p.jpg | 0 | 1 2 | a.jpg | 3 | 4 3 | v.png | 2 | 0 so the variable to set dynamicall should be compared to gid (id from the gallery-table). i found that the problem with two joins is, that the group-by-methods get applyed to all rows, so i cannot differ the amount of comments and ratings. the only possibility is to find it out via php, but this is not what i'm aiming for.. thanks for your answer! -mathew # life would be easier if i knew the source code... > Von: "Robert Sossomon" <robert@xxxxxxxxxxxx> > Organisation: Garland C Norris Company > Antworten an: <robert@xxxxxxxxxxxx> > Datum: Thu, 29 Jan 2004 11:17:55 -0500 > An: <grillen@xxxxxxxxxxxxxx>, "'PHP-DB'" <php-db@xxxxxxxxxxxxx> > Betreff: RE: multiple tables and amounts > > // 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 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
