0

can we use the join operation to create a table on a Database (called DB1 in the example) based on two tables from Database another database (called DB2 in the example) ? If yes, then how to do ?

Both databases are on the same server and DBMS is the same. ID1 and ID2 based on table1 in DB2; ID1 and Name based on table2 in DB2

Here´s what I tried out already (but it´s not working)Made from point of view that I´m using DB1:

INSERT INTO table3 (ID1,ID2,Name)
SELECT ID1,ID2, DB2.table2
FROM DB2.table1;
LEFT JOIN DB2.table2
    ON ID1=ID1;

Thanks for your help again!

Drew
  • 24,851
  • 10
  • 43
  • 78
AbsoluteBeginner
  • 485
  • 4
  • 13
  • I get an error: "Code Error Code: 1054. Unknown column 't2.value' in 'field list'" - what does that mean? (My column "Name" in the example is originally called value) – AbsoluteBeginner Oct 10 '16 at 10:15
  • You need to remember that the semi-colon ends the statement. So you orphan the remainder and your aliases are not complete (tables not brought in) – Drew Oct 10 '16 at 10:20
  • So it is the equiv of a multi-query but the first one bombs (1054). the fragment after it would be jibberish but it never gets there. – Drew Oct 10 '16 at 10:21
  • deleted the semicolon and now get the result I wanted: Big thanks (again) :) ! – AbsoluteBeginner Oct 10 '16 at 10:22
  • Another Question: can I automatically update the data in table 3 coming from table 1 and 2 (let´s say every 24 hours)? – AbsoluteBeginner Oct 10 '16 at 10:30
  • Sure. [One](http://stackoverflow.com/a/37901661/1816093) and [Two](http://stackoverflow.com/a/32508935/1816093) – Drew Oct 10 '16 at 10:32
  • I rolled back your question. No [Chameleon](http://meta.stackexchange.com/questions/43478) . Sure I gave you a comment above to help. But don't change the base question. It invalidates the peoples' answers below. Now let's stick a fork in this question. It is over. – Drew Oct 10 '16 at 10:35
  • Sorry- I haven´t known that: thanks for your help again :) – AbsoluteBeginner Oct 10 '16 at 10:36

2 Answers2

1

I think you should use proper aliases in your query:

INSERT INTO table3 (ID1, ID2, Name)
SELECT t1.ID1, t1.ID2, t2.Name
FROM DB2.table1 t1
LEFT JOIN DB2.table2 t2
    ON t1.ID1 = t2.ID1
Drew
  • 24,851
  • 10
  • 43
  • 78
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

you can create table at the same time insert records:

CREATE TABLE table3 AS
SELECT t1.ID1, t1.ID2, t2.Name
FROM DB2.table1 t1
LEFT JOIN DB2.table2 t2
    ON t1.ID1 = t2.ID1
Drew
  • 24,851
  • 10
  • 43
  • 78
Piotr Rogowski
  • 3,642
  • 19
  • 24