Simple Count with joins

[hendoyeah]

Hi all

Trying 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.

[artacus]

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.

[fenway]

Why not group by both artist & album?

[artacus]

* 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.

[fenway]

Maybe I missed the point -- but I don't think WITH ROLLUP is necessary... I re-read the original post twice.

[artacus]

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 :)

[fenway]

No, that's not what I meant.. I see the problem now.  Fish slap accepted.