In the multi-table join dialogue there are 3 types of joins represented which correspond precisely to 3 joins available in SQL. Those are left, right and inner joins. When a date shows up in both datasets it will be in all 3 of these joins, so if your datasets correspond 1 to 1 with regard to the join field and there are no missing records it doesn't matter which of these is used. When there are more are multiple matches there are some additional subtleties, but since each of your data sets will have at most one record per date it comes down to what is done when a date shows up in just one of the datasets.
Since you connected to the Quote sheet before the Treasury Rate sheet, Quote is thought of as being to the left of Treasury Rate. This has a bit more meaning if you were to look at the SQL query written out across a single line since the first table would be on the left.
- A left join will not filter the table on the left, so if there is no match in the other table it will keep the row but any fields coming from the right table will contain nulls. If a row in the right table doesn't have a match, then it will be filtered by a left join.
- An inner join will bring back only rows which have a match in both the left and right tables, so may filter rows from either the left or right tables if there is no match. Inner join is the default for a multi-table join.
- A right join is symmetrical to a left join. Left joins get more press, because it is more common to bring in the more comprehensive datasource first, but there is nothing wrong from a database (or an algebraic) standpoint with doing it the other way around. Since you brought the Treasury Rates sheet in second a Right Join matches your description and the pdf. I attached a workbook with this change made, the Join type dropdown in the multi-table join dialogue is also indicated in the image below. To get to this window right click the connection and select edit connection, then click on the treasury rate table and the edit button.
Let me know if this sorts your problem out. Apologies if I over explained, I was just worried you would bring in the tables in a different order next time and be confused why it didn't work anymore.
ShowMissingValues_NS.twbx 59.4 KB
Thank you so much for the detailed explanation (its very helpful) and twbx! That worked.
No problem. Glad I could help.