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

2. services table

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.

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

any help will be much appreciated, thanks.

CodePudding user response：

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

CodePudding user response：

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)