Hi,
I'm testing the querying of three tables for info related to songs. One table holds the song info, one holds the song reviews, and one holds band information. Here's the query:
$songid = "28080";
$reviewcnt = "SELECT COUNT(r.review) as revcount, r.review, b.bandid, s.sdescr FROM review r, bandpage b, song s WHERE s.songid = '$songid' AND s.bandid = b.bandid AND s.songid = r.songid GROUP BY s.songid";
$getrc = mysql_query($reviewcnt, $conn) or die(mysql_error());
$r = mysql_fetch_assoc($getrc);
$revcount = $r['revcount'];
$sdescr = $r['sdescr'];
$bandid= $r['bandid'];
echo "$revcount $sdescr $bandid ";
I first tried it without the GROUP BY part and it gave an error, so I added "GROUP BY s.songid" and it worked. The thing I don't understand is that the bandpage table doesn't contain any songid's. The song and band tables are related by bandid, but not songid. Can somebody please explain?
Also, I'm almost certain I read somewhere that COUNT from multiple tables isn't possible w/o subqueries. Has anybody else heard that?
Thank you very much.
