Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
webchris
Starting Member
2 Posts |
 Posted - 2011-08-28 : 19:27:09
|
| 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:SongsID | Song_NameGenresID | Genre_NameGroupsID | Group_NameSong_Genres (links songs table to genres table)Song_ID | Genre_IDGroup_Linking (links groups table to genres table)Genre_ID | Group_IDI 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.id, songs.song_name FROM songs, song_genres, group_linking WHERE songs.id = song_genres.song_id AND song_genres.genre_id = group_linking.genre_id AND group_linking.group_id = 1I think I need a LEFT JOIN but not sure how to do that with the 3 tables. Any help would be greatly appreciated! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-28 : 19:37:45
|
| FROM Songs WHERE ID IN (select Song_ID from Song_Genres inner join Group_Linking ON Song_Genres.Genre_ID = Group_Linking.Genre_ID WHERE Group_ID = 1)Easiest way to get the songs without duplication. If you join you'll have to eliminate the duplicates that the join creates.--Gail ShawSQL Server MVP |
 |
|
|
webchris
Starting Member
2 Posts |
Posted - 2011-08-28 : 19:56:56
|
| Thanks GilaMonster! That was perfect, and I learned something. :) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
|
|
|
Need help with query - joining 3 tables