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.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 = 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!