I want to create a database table which will have multiply information. One of these information is phones. How can I create a database table that inside the phones column will contain let’s say sub-columns, because a user may have more than one phone and be stored in the same row for the same ID.
Below is an image of what I mean and how I want my table to be…
ALL THE ANSWERS ARE HELPFUL UNFORTUNATELY I CAN PICK ONLY ONE BUT ALL ANSWERED HELPED ME
Advertisement
Answer
MS SQL Server 2017 Schema Setup:
create table my_user ( ID int primary key, FirstName varchar(255), LastName varchar(255), address varchar(max) ) create table user_phones ( ID int primary key, userId int references my_user(ID), countrycode int, phone varchar(max), type varchar(255) ) INSERT INTO my_user(ID,FirstName,LastName,address) VALUES(1,'test','test','test'); INSERT INTO user_phones(ID,userId,countrycode,phone,type)VALUES(1,1,99,'000099900','mobile'); INSERT INTO user_phones(ID,userId,countrycode,phone,type)VALUES(2,1,99,'99900000','home'); INSERT INTO user_phones(ID,userId,countrycode,phone,type)VALUES(3,1,99,'000009999','fax');
Query 1:
select * from my_user u left join user_phones p on u.ID=p.userId
| ID | FirstName | LastName | address | ID | userId | countrycode | phone | type | |----|-----------|----------|---------|----|--------|-------------|-----------|--------| | 1 | test | test | test | 1 | 1 | 99 | 000099900 | mobile | | 1 | test | test | test | 2 | 1 | 99 | 99900000 | home | | 1 | test | test | test | 3 | 1 | 99 | 000009999 | fax |