How to merge SQL when the key variable repeats in one of the tables?

Question

I have a dataset with two tables. In the first, I have information of workers and in the second of firms. Each of the workers has a id of the firm you belong to. I would like to create a new table from the merge of the base of workers with the base of firms, keeping the information of the two tables. Here is a minimum replicable example of the tables:

CREATE TABLE workers (id INT, name VARCHAR(100), sex VARCHAR(100), age VARCHAR(100));
INSERT INTO workers (id,name,sex,age)
VALUES (1,"W. White","male",54);
INSERT INTO workers (id,name,sex,age)
VALUES (2,"Hank Schroder","male",51);
INSERT INTO workers (id,name,sex,age)
VALUES (2,"Gus Fring","male",50);
INSERT INTO workers (id,name,sex,age)
VALUES (1,"Skyler","male",44);

CREATE TABLE firms (id INT, name VARCHAR(100), capital INT);
INSERT INTO firms (id,name,capital)
VALUES (1,"Bank Warburg",13051);
INSERT INTO firms (id,name,capital)
VALUES (2,"Atlas Security Information",42094);

I tried first to use the operator IN:

SELECT * FROM workers WHERE id IN (SELECT id FROM firms);

This command returns the desired structure, but without firm data (i.e., name and capital):

1|W. White|male|54
2|Hank Schroder|male|51
2|Gus Fring|male|50
1|Skyler|male|44

I also tried to:

MERGE firms AS TARGET
USING workers AS SOURCE 
ON (TARGET.id = SOURCE.id)

That returns the error Error: near line 17: near "MERGE": syntax error. As if he wasn’t recognizing the keyword "MERGE".

How can I merge keeping firm data?

Answer 1

There may be some differences depending on the SQL language you are using, but this should solve your problem::

create table firms_workers (
    id INT, 
    firm_name VARCHAR(100), 
    capital INT, 
    worker_name VARCHAR(100), 
    sex VARCHAR(100), 
    age VARCHAR(100));

insert into firms_workers (id, firm_name, capital, worker_name, sex, age)
select f.id, f.name, f.capital, w.name, w.sex, w.age
from firms f
inner join workers w on f.id = w.id; 

What I am doing is joining the two tables by the column id, which gives 4 lines with the data you provided. This data is inserted in a new table with the name "firm_workers"

Please test and any questions please let me know