0

I have the following tables and I need to join them:

Table A

+----+----------+--------+
| ID | Period   | Value  |
+----+----------+--------+
| 1  |2009-02-01|  20.3  |
| 2  |2009-03-01|  22.5  |
| 3  |2009-04-01|  17.4  |
| 4  |2009-05-01|  16.5  |
| 5  |2009-06-01|  26.5  |
| 6  |2009-07-01|  35.4  |
+----+----------+--------+

Table B

+----+----------+--------+
| ID | Period   | Value  |
+----+----------+--------+
| 1  |2009-04-01|  57.1  |
| 2  |2009-05-01|  56.5  |
| 3  |2009-06-01|  59.8  |
| 4  |2009-07-01|  55.4  |
+----+----------+--------+

Table C

+----+----------+--------+
| ID | Period   | Value  |
+----+----------+--------+
| 1  |2009-03-01|  82.5  |
| 2  |2009-04-01|  87.4  |
| 3  |2009-05-01|  86.7  |
+----+----------+--------+

My output table is already created empty table and looks like this:

Table D

+----+----------+--------+--------+--------+
| ID | Period   | ValueA | ValueB | ValueC |
+----+----------+--------+--------+--------+

As table A contains most of the records I want to use it as primary table. The desired result is as follow:

Table D

+----+----------+--------+--------+--------+
| ID | Period   | ValueA | ValueB | ValueC |
+----+----------+--------+--------+--------+
| 1  |2009-02-01|  20.3  | NULL   | NULL   |
| 2  |2009-03-01|  22.5  | NULL   | 82.5   |
| 3  |2009-04-01|  17.4  | 57.1   | 87.4   |
| 4  |2009-05-01|  16.5  | 56.5   | 86.7   |
| 5  |2009-06-01|  26.5  | 59.8   | NULL   |
| 6  |2009-07-01|  35.4  | 55.4   | NULL   |
+----+----------+--------+--------+--------+

I'm very new to MySQL. I looked to similar questions in the forum and tried to figure it out for myself but with no success.

Any help appreciated.

ANSWER

Ok. Few things. First the question isn't duplicate! The thing looks more like a speedy moderator than duplicate question. And No, I didn't find this answer anywhere on this website and for sure not in the suggested from the moderator answer.

Now the interesting part. With the help of the creative moderator after spending few hours finally I got it work. As I said in the original question table D already exists. This is because the model is created and managed by third party application. In this case Django. This is an important point as otherwise the operation will be different. I also have more than one schema on the server. I think In this case the best way for this query is by using alias as follow:

insert into my_schema.table_d(period, valueA, valueB, valueC)
select A.Period, A.Value, B.Value, C.Value
from my_schema.table_a A

left join my_schema.table_b B
on A.Period = B.Period
left join my_schema.table_c C
on A.Period = C.Period
sretko
  • 611
  • 2
  • 7
  • 18
  • What is this? Bait for votes? – oxygen Mar 12 '17 at 13:02
  • @Tiberiu-IonuțStan What I'm seeing is just well formed question! It expects good and clear answer. You probably speak of the way you have got your reputation! – sretko Mar 12 '17 at 14:25

1 Answers1

0

You are looking for a left join:

insert into d( . . . )
    select . . .
    from a left join
         b
         on a.period = b.period left join
         c
         on a.period = c.period;

Fill in the columns where the . . . are.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried this one but it returns No database selected error: 'insert into d(period, value1, value2, value3) select a.Period, a.Value, b.Value, c.Value from a left join b on a.Period = b.Period left join c on a.Period = c.Period' – sretko Mar 12 '17 at 13:35