hendoyeah Posted December 18, 2006 Share Posted December 18, 2006 Hi allTrying to do a count joining a couple of tables.|---------------|| user_library ||---------------||userid ||ablbumid ||---------------||---------------||artists ||---------------||artistid ||artist_name ||---------------||---------------|| albums ||---------------||albumid ||artistid ||ablbum_name ||---------------|Want to return a query listing all albums a user has according to the "user_library" table, including the fields albmum_name, artist_name. That part is easy. However i also want to include a count field for each row showing the number of albums a user has by that particular artist. Something like:|--------------------------------------------------------------------------------|| userid | album_name | artist_name | albums_by_this_artist_in_this_query ||--------------------------------------------------------------------------------|| 1 | Album1 | Artist1 | 2 ||--------------------------------------------------------------------------------|| 1 | Album2 | Artist1 | 2 ||--------------------------------------------------------------------------------|| 1 | Album3 | Artist2 | 1 ||--------------------------------------------------------------------------------|I'm sure its simple, but i've never done this. thank you. Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/ Share on other sites More sharing options...
artacus Posted December 18, 2006 Share Posted December 18, 2006 Well you'll either need to do a group by (group by artist_id w/ COUNT(albums.albumid), which would put the all of the albums by this artist on a single line) not what you want according to your example above.So your other choice is to do a subquery where you count the number of albums by that artist... assuming your mysql server is new enough to handle subqueries. Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/#findComment-144019 Share on other sites More sharing options...
fenway Posted December 19, 2006 Share Posted December 19, 2006 Why not group by both artist & album? Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/#findComment-144102 Share on other sites More sharing options...
artacus Posted December 19, 2006 Share Posted December 19, 2006 * Holds trout prepares to commit hairy kary... changes mind and slaps fenway*Lacking the ambition to test it, I don't think that will work unless you use WITH ROLLUP... And if I said, use WITH ROLLUP, he'd just ask how to do that, and it would just make more work for me. Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/#findComment-144172 Share on other sites More sharing options...
fenway Posted December 19, 2006 Share Posted December 19, 2006 Maybe I missed the point -- but I don't think WITH ROLLUP is necessary... I re-read the original post twice. Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/#findComment-144580 Share on other sites More sharing options...
artacus Posted December 19, 2006 Share Posted December 19, 2006 Well COUNT() will only work with the members of the group. But since you grouped it down to the album level, which you'd need to do to display every album, then COUNT() will always display 1....hmm... unless you left join the album table back on itself like so:[code]LEFT JOIN albums AS alb2 ON alb.artistid = alb2.artistid AND alb.albumid <> alb2.albumid[/code]If that's what you meant, I humbly take my fish slap back :) Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/#findComment-144645 Share on other sites More sharing options...
fenway Posted December 19, 2006 Share Posted December 19, 2006 No, that's not what I meant.. I see the problem now. Fish slap accepted. Link to comment https://forums.phpfreaks.com/topic/31164-simple-count-with-joins/#findComment-144789 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.