Jump to content

Archived

This topic is now archived and is closed to further replies.

mojito

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

Recommended Posts

[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

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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\" /]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.