[!--quoteo(post=376680:date=May 24 2006, 10:36 AM:name=mojito)--][div class=\'quotetop\']QUOTE(mojito @ May 24 2006, 10:36 AM)
the bit i dont get is the following bit...
count(l.pid) as Cnt from albums a, pictures p, pictures l where a.aid = p.aid and a.pid = p.pid and a.aid = l.aid group by a.aid order by a.aid
well, when you do an aggregate function such as COUNT(), SUM(), AVG() or any others, you've got to group the results based on whatever columns you're trying to pull out. so, "COUNT(l.pid) as Cnt" is simply saying, return the count of column "pid" from table "l" (pictures) and name the result column "Cnt". then, in the FROM clause, all the tables are given a single letter pseudonym for easy reference. that's where the "l." and "p." stuff comes from when referencing a column name.
finally, you have your "GROUP BY" clause which usually needs to include all columns selected besides the COUNT() itself.
hope this helps.