CODEWITHSUNDEEP
mysqlsql
mickey4691
mickey4691

Reputation: 9

SQL query to add multiple values into an attribute

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');

Upvotes: 0

Views: 3346

Answers (3)

Saurabh
Saurabh

Reputation: 73589

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.

Upvotes: 2

Ruben Ravnå
Ruben Ravnå

Reputation: 697

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

Upvotes: 0

thexacre
thexacre

Reputation: 692

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.

Upvotes: 0

Related Questions