SQL join without getting the cartesian product

[mellenburg]

I have two table that I would like to merge. They have no common variable on which to do a join. Each table has the same number of records and I want one table with all the data in the other two tables. The code:

create table newtable
select t1.*, t2.*
from table1 as t1, table2 as t2

gives me the cartesian product of the two tables. How can I modify the code so that I get the table I want?

 

[r937]

create table newtable
select t1.*
from table1 as t1

insert into newtable
select t2.*
from table2 as t2


rudy

 

[sleipnir214]

If you use "tablename, tablename" in a SELECT clause without a limit on the join, MySQL assumes a cartesian product.

Anyway, you are accumulating data, not performing relational operations on it. I recommend that you perform two queries:

create table newtable select * from table1;
insert into table newtable select * from table2;
Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[JustKIDn]

Are you talking about an append, so you combine two tables into one?

I know I've done this with Access. I'm not exactly sure how to do that in MySQL.

But if I were to try, I would add an integer field to both tables (call them ID1 and ID2). And then order both tables by whatever I needed. Fill in the ID's with sequential numbers. Then join the two tables on the ID numbers.

That's what I would do!
tgus

____________________________
Families can be together forever...