inserting data from multiple table

Question

I currently have two tables which they do not have anything in common, and I would like to get data from both tables and insert it into the 3rd table.

table A

CustomerId | CustomerName | PhoneNumber

table B

ProductId | ProductName | priceA | PriceB | PriceC

table C

id | customerID | productID

The problem now is that whenever I insert a new product (table B), I would like to have it assign to each customer (table A)

So let says

table A

 c1 | aa | 1234 c2 | bb | 3456 c3 | cc | 3123

and table B

 p1 | candy | 1.1 | 1.2 | 1.3 p2 | bag | 5.5 | 5.6 | 5.7 p3 | key | 3.3 | 3.1 | 3.5

and I would like it to have each customer assigned to all products like the following:

table C

 1 | c1 | p1 2 | c1 | p2 3 | c1 | p3 4 | c2 | p1 5 | c2 | p2 6 | c2 | p3 7 | c3 | p1 8 | c3 | p2 9 | c3 | p3

can I please get any suggestion of how i should do this. my original thought was to use JOIN, but there is nothing in common that I can join, is there a better and faster way to do this?

How about using CARTESIAN JOIN or the CROSS JOIN?

ambianBeing
– ambianBeing

2019-09-01 07:54:52 +00:00
Commented Sep 1, 2019 at 7:54 — ambianBeing
– ambianBeing, Commented Sep 1, 2019 at 7:54

Helper · Accepted Answer · 2019-09-01 09:00:11Z

The above scenario is perfectly matched for a cross join.

This is how cross join works:

Now the below line of query is going to select the required result set that is to be inserted to the table C.

SELECT A.customer_id, B.product_id FROM A CROSS JOIN B

Just insert the result set obtained by the above query:

INSERT INTO C SELECT A.customer_id, B.product_id FROM A CROSS JOIN B

Mureinik · Accepted Answer · 2019-09-01 07:54:49Z

This is a perfect fit for a cross join - a join without a condition, that matches every row of one table to every row on the other:

INSERT TO c (customer_id, product_id) -- Assuming c.id is auto incremented... SELECT a.customer_id, b.product_id FROM a CROSS JOIN b

P.Salmon · Accepted Answer · 2019-09-01 10:09:26Z

I guess you could add customers and/or products at any time. An initial load using a cross join would be useful. As new customers or products are onboarded then an insert..on duplicate key (or insert..ignore if you prefer) may be sufficient to update table c. If that's too slow you might consider truncating table c before doing a full load (using cross join) or adding triggers to table a and table b to do the job. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html You will need to ensure you have a key to be checked so table c should have a unique key on customerID,productID

Collectives™ on Stack Overflow

inserting data from multiple table

table A

table B

table C

table A

and table B

table C

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

table A

table B

table C

table A

and table B

table C

3 Answers 3

Comments

Comments

Comments

Related