Merging multiple more than 2 tables with an outer join

[Anonymous]

So the current situation (though I could imagine this will come up a lot) is that I want to get a table of weather patterns for a location. I have 3 tables:

1. max_temp: Has columns Year, Month, Day and max_temp
2. min_temp: Has columns Year, Month, Day and min_temp
3. rainfall: Has columns Year, Monh, Day and rainfall

I want to make these into one table that will be called _combined_weather with the columns Year, Month, Day, max_temp, min_temp and rainfall.

This should be done as an outer join, as not all days have data. So I would do:

1. A "Merge queries as new" and combine max_temp and min_temp.
2. Expand the min_temp details, and rename the Year, Month and Day columns to max_temp.Year, max_temp.Month and max_temp.Day so that we can tell where the data came from.
3. Now when I go to add rainfall, when I do the join, I would join it to the fields on max_temp.

The issue with this, is that if a day is missed on max_temp and not min_temp or rainfall, it will create 2 rows.

How can you get around this?

[v-jiascu-msft]

Hi @Anonymous,

There is a solution. But firstly I would say we usually create relationship among these tables in your scenario. Especially the data size is large. 

The solution is as follows.

1. Create a new table which contains all the Year, Month, Day, which is continuous. 

2. Meger all other three tables to this new table using Left Outer Join. 

3. Expand only the desired column. 

4. Remove the columns that have nothing in all the three columns. 

Please refer to the snapshot below.

Best Regards,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

There is a new solution. Please download the demo from the attachment.

1. Append all the tables as a new table.

2. Group by the table by Year, Month, Day. Please refer to the snapshot below.

Best Regards,

[Anonymous]

This is the correct solution and the logic behind it is good. Thanks for that.

[Anonymous]

But we need to create the dates from the other 3 tables, thats the point. So you would start with a blank table with year month day. You can't do a left outer join, otherwise there is nothing to join onto. If you do a right outer join, it keeps the value on the left, so you get the correct number of rows, but the year month and day fields are all null as they weren't on the left, we want to join them in on the left (get the values from the right and insert them on the left table).