could someone walk me through the last part of this statement.

[mojito]

[code]mysql_query("select a.aid as AlbumID, a.Title as Title, a.pid as PictureID, p.thumbname as Picture, 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", $connection); [/code]

the bit i dont get is the following bit...
[code]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[/code]

thanks for any help

[obsidian]

[!--quoteo(post=376680:date=May 24 2006, 10:36 AM:name=mojito)--][div class=\'quotetop\']QUOTE(mojito @ May 24 2006, 10:36 AM) [snapback]376680[/snapback][/div][div class=\'quotemain\'][!--quotec--]
the bit i dont get is the following bit...
[code]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[/code]
[/quote]

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.

[fenway]

If you were to write out this query "properly" (using uppercase and proper JOIN syntax), it might make more sense to you:

[code]SELECT
a.aid AS AlbumID
, a.Title AS Title
, a.pid AS PictureID
, p.thumbname AS Picture
, COUNT(l.pid) AS Cnt
FROM
albums AS a
LEFT JOIN pictures AS p ON ( a.aid = p.aid AND a.pid = p.pid )
LEFT JOIN pictures AS l ON ( a.aid = l.aid )
GROUP BY a.aid
ORDER BY a.aid[/code]
Of course, obsidian's explanation still applies.

[mojito]

THANK YOU SO MUCH TO BOTH OF YOU.

Im sure I can get it now, too busy to get on this problem right now, but im sure I can understand the results from this now.

If only the original coder had commented as such or explained a little. Commenting code is good, at least for me.

thanks again

m [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]