我应该使用什么SQL操作？(What SQL operation should I use?)

我应该使用什么SQL操作？(What SQL operation should I use?)

我有两张桌子：

TABLE 'songs' song_id --some other columns about this song ------- 1 2 3 TABLE 'song_ownership' user_id song_id ------- ------- 28 1 28 3 538 1 234 2

我有兴趣执行查询，其中给定user_id我已经返回了他们拥有的所有歌曲。 例如，您可以看到用户28拥有两首歌曲。

顺便说一下，这是我知道如何规范化这个表的最佳方法，并且对于如何更传统地存储这些数据的建议持开放态度（我只是在学习SQL）。 这是典型的表设置吗？

I have two tables:

TABLE 'songs' song_id --some other columns about this song ------- 1 2 3 TABLE 'song_ownership' user_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

假设同一个用户不能拥有两次相同的歌曲。 到目前为止，你的规范化看起来很好 您的song_ownership表将是“多对多”表，并且（如果歌曲用户关联是唯一的），您可以在两列上放置复合主键，并且您的用户将位于单独的表中。

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.