Hi
I want to make a top ten songs with two tables but i dont know how to do it.
I have a album table , songs table and a ratings table,the common field is song_id for songs and ratings and album_id for songs and albums
I want to show, bandname and albumname (from albums)songname (from songs), and the averageof all ratings (from ratings), for example 4 ratings: 9+4+6+7/4=6.5
Ordering by ratings average.
Can you help me, thanks

Hello,

SQL query will look something like this:

SELECT
S.title AS song_name,
A.title AS album_name,
AVG(R.rating) AS rating
FROM
songs S INNER JOIN ratings R
ON S.song_id = R.song_id
INNER JOIN albums A
ON S.album_id = A.album_id
GROUP BY
S.song_id

Regards,
ARI Soft

thanks for your answer but i think it's more difficult than i thought.
i've looking the tables and the relations and fields are like this.
I don't know if it's clear enough, but i want to make two ratings lists one with songs and another with albums
I attach an excel file
Thank you again for your pacience

artists Albums songs ratings

Id artist_name Id name artist_id Id album_id name Id Album_id points type
1 kraftwerk 1 Computer world 1 450 1 pocket calculator 1 450 5 song
2 U2 2 man machine 1 450 1 pocket calculator 2 450 8 song
3 Madonna 3 like a virgin 3 120 3 noname 3 3 9 album


relations
artists albums albums songs songs ratings type=song
id artist_id Id album_id id album_id

albums ratings type=album
id album_id


result for song rating

artist album song average
kraftwerk computer world pocket calculator 6,5

result for albums rating

artist album average
madonna like a virgin 9

Send SQL dump of your tables by email.

Regards,
ARI Soft

i dont know how to do that
i can export the table structure to word, for ex.

This video tutorial shows how to create an SQL dump with help of PHPMyAdmin tool which is usually available in hosting control panel (like cPanel).

Regards,
ARI Soft