MySQL JOIN on three tables

[yorizz]

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

 

[littleBird]

Below is the database structure and the SQL required to get your desired result. Hope this helps...

CREATE TABLE `genre` (
`genre_id` int(2) NOT NULL auto_increment,
`genre_title` varchar(250) default NULL,
PRIMARY KEY (`genre_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

insert into `genre`(`genre_id`,`genre_title`) values (1,'soul');
insert into `genre`(`genre_id`,`genre_title`) values (2,'funk');
insert into `genre`(`genre_id`,`genre_title`) values (3,'house');


CREATE TABLE `rating` (
`rating_id` int(5) NOT NULL auto_increment,
`song_id` int(5) default NULL,
`rating` int(1) default NULL,
PRIMARY KEY (`rating_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


insert into `rating`(`rating_id`,`song_id`,`rating`) values (1,1,5);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (2,1,2);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (3,1,4);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (4,2,5);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (5,2,1);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (6,2,4);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (7,3,5);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (8,3,4);
insert into `rating`(`rating_id`,`song_id`,`rating`) values (9,3,5);

CREATE TABLE `song` (
`song_id` int(5) NOT NULL auto_increment,
`genre_id` int(2) default NULL,
`song_title` varchar(250) default NULL,
PRIMARY KEY (`song_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

insert into `song`(`song_id`,`genre_id`,`song_title`) values (1,1,'Lets spend the night together');
insert into `song`(`song_id`,`genre_id`,`song_title`) values (2,2,'Family Affair');
insert into `song`(`song_id`,`genre_id`,`song_title`) values (3,3,'Love and Happiness');


SELECT S.song_id, S.song_title, AVG(R.rating) AS avg_rating, G.genre_title FROM song S
JOIN rating R ON R.song_id=S.song_id
JOIN genre G ON G.genre_id=S.genre_id
GROUP BY S.song_id

 

[yorizz]

Hi littleBird,

You rock! that does exactly what I wanted when I add 'LEFT' before the first join (also want to show results when there is no rating).

Thanks so much!

 

[littleBird]

Happy Days