Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011

    Unanswered: Need help with query - joining tables

    I am familiar with joining tables, but this particular setup has really gotten to me. I don't know if I'm overthinking it or what. Here is an example of my structure:

    ID | Song_Name

    ID | Genre_Name

    ID | Group_Name

    Song_Genres (links songs table to genres table)
    Song_ID | Genre_ID

    Group_Linking (links groups table to genres table)
    Genre_ID | Group_ID

    I have genre "groups" where you might combine country and western, or rap and r&b into a similar group to pull together. Each song can be listed in multiple genres, and each genre can be in multiple groups. That's why I need the 2 linking tables.

    Now what I am trying to do is pull all songs that are in group 1. It sounds really easy, but I am not able to wrap my head around it yet. I tried the simplest way I could think of, but its duplicating my song for each genre its in.

    SELECT, songs.song_name FROM songs, song_genres, group_linking WHERE = song_genres.song_id AND song_genres.genre_id = group_linking.genre_id AND group_linking.group_id = 1

    I think I need a LEFT JOIN but not sure how to do that with the 3 tables. Any help would be greatly appreciated!

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by webchris View Post
    ... but its duplicating my song for each genre its in.
    you just need to add DISTINCT to your query

    and of course it's a lot easier to write and to understand if you used explicit JOIN syntax rather than the old-style deprecated comma list syntax
         , songs.song_name 
      FROM group_linking
      JOIN song_genres
        ON song_genres.genre_id = group_linking.genre_id
      JOIN songs
        ON = song_genres.song_id
     WHERE group_linking.group_id = 1 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Thanks r937! I have been using JOIN more and more lately, but this was the first case where I needed 3 tables in one query. Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts