SQL query to add multiple values into an attribute

Question

I appreciate any help. I am creating a database query and I would like to add something. I created two tables(users&videos) and I would like to insert video_id into users table but however I would like one user to have multiple videos. For example, user_id 1 should have all three videos. Any help?

create table users ( user_id int, username varchar(50), video_id int, primary key (user_id) );

insert into users(user_id,username,video_id) values (1,'trex',1&2&3); This line is incorrect.

create table videos ( video_id int, video_title varchar(100), primary key (video_id) );

insert into videos(video_id,video_title) values (1,'Math tutor'),(2,'SQL joins'),(3,'Fastest cars');

Saurabh · Accepted Answer · 2014-04-27 08:20:00Z

2

I think in that case, you should normalize your table users, remove video_id from here and create one table to have users to video mapping, something like this:

create table user_videoes (
user_id int,
video_id int);

By this approach, one user can have multiple videos and one video can belong to multiple users.

edited Apr 27, 2014 at 8:20

answered Apr 27, 2014 at 7:54

Saurabh

73.8k44 gold badges193 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickey4691 Over a year ago

Thank a lot. This is useful and much simpler.

thexacre · Accepted Answer · 2014-04-27 07:53:40Z

0

If a video can only belong to one user then you should do it the other way around, ie. the user_id field is in the video table. That way one user can have many videos.

answered Apr 27, 2014 at 7:53

thexacre

7024 silver badges9 bronze badges

1 Comment

mickey4691 Over a year ago

I have to do some multiple stuff. Like many videos belonging to a channel that is why I used user as an example. Thank you for your input.

Ruben Ravnå · Accepted Answer · 2014-04-27 07:55:52Z

0

You can't insert 1&2&3 as int on video_id

You need to insert three rows, to get three video_id's, and one for each video

insert into users(user_id,username,video_id) values (1,'trex',1);
insert into users(user_id,username,video_id) values (1,'trex',2);
insert into users(user_id,username,video_id) values (1,'trex',3);

But then, user_id can't be primary key. Use rather one id column whit autoincrement

answered Apr 27, 2014 at 7:55

Ruben Ravnå

6977 silver badges19 bronze badges

2 Comments

mickey4691 Over a year ago

Thank you but I did not want to repeat such values.

Clockwork-Muse Over a year ago

Well, he could insert it with Boolean &, but then he'd only be able to store 32 videos (assuming a 32-bit integer). And it'd be a pain to get at...

Collectives™ on Stack Overflow

SQL query to add multiple values into an attribute

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related