To read this series from the beginning, start with our intro post.
The previous post in this series gave an example of the simplest possible join—a join on a single column. But life isn’t always that easy. In real-world Dynamics tables, the key often includes multiple columns. (For example, when a customer has multiple ship-to addresses.)
Unfortunately, the Northwind sample database (which we’ve been using for this series) doesn’t have an example of this. So for the purposes of this post, I created two additional tables: ship-to address and order header.
How to Create a Multi-Column Join
I’ve titled the ship-to table, Red3Ship. And I’ve set up two customers, each with two ship-to addresses:
Now, let’s look at the Order table. We have one order for each CustomerID/ShipToID combination:
First, let’s add the customer name (i.e. CompanyName) to the query:
We still have four records.
Now, let’s add the ship-to name (i.e. ShipToName):
The good news is we have the ship-to name. The bad news is we’ve doubled the record count. We now have eight records.
You might guess (based on my example in the previous post) that the problem arose from not specifying the join properly. And you’d be right!
We can fix this in two steps.
First, in addition to the ShipToID from the Order table, we include the ShipToID from the Red3Ship table:
We can see here that while each order has one ship-to, our query doesn’t bother matching the ship-tos. It joins to both records.
Therefore, we need to fix the join by adding a join on ship-to, not just on customer:
And we’re back to four records.
The point is this: You need to know the complete key when you join. Otherwise, your query will return too many records.
In other words, you want “the key, the whole key and nothing less than the whole key.”