Hi, I am new here and I very stuck on a MySQL query which involves 3 tables.
1st table is a song table:
song_id (int) //unique
song_name (varchar) //name of the song
song_genre (int) //genre this song belongs to
2nd table is a rating table
rating_id (int) //unique
rating_song_id (int) // the id of the song i want to rate
rating_rating (int) //the marks for the song
3rd table is the genre table
genre_id //unique
genre_name //the name of the genre
From this database I want the extract the following information:
the song id
the name of the song
the name of the genre for the selected song
the average rating for the selected song
With the next query I am getting close, it gives me the song information and the average rating, even when there hasn't been any. So far so good, but no matter where I try to squeeze in the genre parameters, it fails on me.
SELECT *, AVG(rating_rating) as avg_rating FROM songs
LEFT OUTER JOIN ratings ON rating_song_id = song_id
GROUP BY song_id;
Has anybody any idea how to solve this or point me in the right direction?
your help is much appreciated, yorizz
1st table is a song table:
song_id (int) //unique
song_name (varchar) //name of the song
song_genre (int) //genre this song belongs to
2nd table is a rating table
rating_id (int) //unique
rating_song_id (int) // the id of the song i want to rate
rating_rating (int) //the marks for the song
3rd table is the genre table
genre_id //unique
genre_name //the name of the genre
From this database I want the extract the following information:
the song id
the name of the song
the name of the genre for the selected song
the average rating for the selected song
With the next query I am getting close, it gives me the song information and the average rating, even when there hasn't been any. So far so good, but no matter where I try to squeeze in the genre parameters, it fails on me.
SELECT *, AVG(rating_rating) as avg_rating FROM songs
LEFT OUTER JOIN ratings ON rating_song_id = song_id
GROUP BY song_id;
Has anybody any idea how to solve this or point me in the right direction?
your help is much appreciated, yorizz