I have 2 tables.
Company Table and Table Categories
Both tables would not be related initially, but then we need to create a third table.
Table category x company.
The query must dump all companies and all categories to the third table.
would be the result.
company1 - category1 company1 - category2 company2 - category1 company2 - category2
... etc ...
What you need to do is what is known as the Cartesian product or CROSS JOIN , let's see a basic example:
create table empresas(
id int,
nombre varchar(255)
);
create table categorias(
id int,
nombre varchar(255)
);
insert into empresas(id, nombre) values (1, 'empresa1');
insert into empresas(id, nombre) values (2, 'empresa2');
insert into categorias(id, nombre) values (1, 'categoria1');
insert into categorias(id, nombre) values (2, 'categoria2');
With a CROSS JOIN we managed to combine each record of one table with each record of the other:
select *
from empresas e
cross join categorias c
order by e.id;
The final result would be 2 x 2 = 4 records, something like this:
id nombre id nombre
--- ----------- --- -----------
1 empresa1 1 categoria1
1 empresa1 2 categoria2
2 empresa2 1 categoria1
2 empresa2 2 categoria2
If you are going to insert this into a ternary table, you should simply list only the ids of each table.
create table empresas(
idempresas int PRIMARY KEY,
nombre varchar(255)
);
create table categorias(
idcategorias int PRIMARY KEY,
nombre varchar(255)
);
create table EmpresaxCategoria(
id int PRIMARY KEY,
idempresas int,
idcategorias int,
nombre varchar(255)
);
declare @UltimoIdInsertadoEmpresa int
declare @UltimoIdInsertadoCategorias int
insert into empresas(id, nombre) values (1, 'empresa1');
SET @UltimoIdInsertadoEmpresa = last_insert_id()
insert into categorias(id, nombre) values (1, 'categoria1');
SET @UltimoIdInsertadoCategorias = last_insert_id()
insert into EmpresaxCategoria (idempresas, idcategorias) values (@UltimoIdInsertadoEmpresa, @UltimoIdInsertadoCategoria)
And this would be the query that brings you your results
select empresas.Nombre, categorias.nombre from EmpresasxCategoria
inner join empresas
on EmpresasxCategoria.idempresas = empresas.idempresas =
inner join categorias
on EmpresasxCategoria.idcategorias = categorias.idcategorias