CODEWITHSUNDEEP
mysqlsqlmariadb
Faizan Aalam
Faizan Aalam

Reputation: 23

How to create pivot table entries for all of the rows available in two separate tables in MYSQL

I need help to create pivot table entries for all of the rows available in two separate tables, basically, I have two separate tables as services and plans as below

  1. plans table
id name description
1 plan one description for plan one
2 plan two description for plan two
  1. services table
id name
1 service one
2 services two

and a pivot table service_plan where I am storing the Id of the plan and service with an extra column to store the price as below.

id service_id plan_id price
1 1 1 200
2 1 2 200

Now I just want a SQL query to assign all the services with all the available plans with a dummy price in it, so the service_plan table will look something like this

id service_id plan_id price
1 1 1 200
2 1 2 200
3 2 1 200
4 2 2 200

any help will be much appreciated, thanks.

Upvotes: 1

Views: 62

Answers (2)

Aafiya Hanafi
Aafiya Hanafi

Reputation: 156

As per your question i think this is the simplest query you can run to get your desired result.

insert into service_plan (service_id, plan_id, price) select s.id, p.id, 200 from services as s cross join plans as p

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17640

why the resistance to a cross join?

drop table if exists PLANS,SERVICES,SERVICE_PLAN;

create table plans
(id int,name varchar(100),  description varchar(100));
insert into plans values
(1, 'plan one'  ,'description for plan one'),
(2, 'plan two'  ,'description for plan two');

create table services
(id int, name varchar(20));
insert into services values
(1  ,'service one'),
(2  ,'services two');

create table service_plan
(id int auto_increment primary key, service_id int, plan_id int, price int not null default 200);

insert into service_plan(service_id,plan_id) 
select s.id,p.id
        from plans p
cross join services s
order by s.id,p.id;

select * from service_plan;

+----+------------+---------+-------+
| id | service_id | plan_id | price |
+----+------------+---------+-------+
|  1 |          1 |       1 |   200 |
|  2 |          1 |       2 |   200 |
|  3 |          2 |       1 |   200 |
|  4 |          2 |       2 |   200 |
+----+------------+---------+-------+
4 rows in set (0.001 sec)

Upvotes: 1

Related Questions