CODEWITHSUNDEEP
mysqlsqldatabase
Asad
Asad

Reputation: 1837

Inserting values into a third table by CROSS JOINing two tables

I want to insert values into a Table_C by cross joining two tables Table_A and Table_B.

Table_A contains two attributes ID and item.
Table_B contains two attributes ID and color.
Table_C contains four attributes ID,item,color,quantity.

All IDs have AUTO INCREMENT.

suppose each item can have all color and I need to create a relation about it. How should I write a query for this? How could I reference a relation cross joining item and color. What my solution is create an intermediate third table joining these two tables and then use that table to insert values into Table_C. But I am pretty sure that there is a better optimized solution for this.

Thanks in advance.

Upvotes: 0

Views: 3528

Answers (2)

Asad
Asad

Reputation: 1837

Here is the query which worked for me.

INSERT INTO Table_C (SELECT null, Table_A.item, Table_B.color, null FROM 
Table_A CROSS JOIN Table_B);

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

No need for a temp table... You can do:

insert into ...
select ... from ...

Write the query you'd need to "fill" that temp table you mention, and insert the rows directly into your final table.

Upvotes: 2

Related Questions