I have a table containing my CD collection information, the simplified structure is following:
id artist album
1 A.R.T The best of A.R.T
2 ATB Big Hits Collection
3 A.R.T My Artistic Life
4 A.R.T You are the only one
How can I build up a query that will contain all CD's as well as a number of albums of each artist. Yes, I'd like to have that info with each row. So, the result would be something like that:
id artist album albumsofartist
1 A.R.T The best of A.R.T 3
2 ATB Big Hits Collection 1
3 A.R.T My Artistic Life 3
4 A.R.T You are the only one 3
I've already tried this
SELECT DISTINCT mh.*, COUNT(mh2.id) AS albumsofartist FROM cd.mh, cd.mh2 WHERE mh.artist= mh2.artist GROUP BY mh2.artist
But it dosen't give correct results.
Thanks for info, Ville M.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
