开发者

What SQL operation should I use?

开发者 https://www.devze.com 2023-02-05 16:25 出处:网络
I have two tables: TABLE \'songs\' song_id--some other columns about this song ------- 1 2 3 TABLE \'song_ownership\'

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

精彩评论

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

关注公众号