I have two tables:
TABLE 'songs'
song_id --some other columns about this song
-------
1
2
3
TABLE 'song_ownership'
user_开发者_JAVA百科id song_id
------- -------
28 1
28 3
538 1
234 2
I'm interested in performing a query where given a user_id
I'm returned all of the songs that they own. You can see that user 28 owns two songs, for example.
Incidentally, this is the best I know how to normalize this table and am open to suggestions on how to store this data more conventionally (I'm just learning SQL). Is this a typical table setup?
select songs.*
from songs
inner join song_ownership on song_ownership.song_id = songs.song_id and
song_ownership.user_id=@user_id
Assuming that the same user can't own the same song twice. Your normalization looks fine so far! Your song_ownership table will be a "many-to-many" table, and (if a song-user association is unique), you can put a compound primary key on both columns, and your users will be in a separate table.
select * -- choose only the columns you're interested to, like user id and song name
from songs
inner join song_ownership on song_ownership.song_id=songs.song_id
where song_ownership.user_id=?
精彩评论