开发者

What SQL operation should I use?

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=?
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜