MySQL JOIN on three tables

Discussion in 'Coding Help' started by yorizz, Jan 12, 2010.

  1. yorizz

    yorizz New Member

    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
     
  2. littleBird

    littleBird New Member

    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
     
  3. yorizz

    yorizz New Member

    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!
     
  4. littleBird

    littleBird New Member

    Happy Days :cool:
     

Share This Page