SQL JOIN Multiple Tables with Concatenation

I often get questions about joining tables in SQL queries.  One particular question that comes up frequently is joining tables on multiple fields, when a user is trying to get unique matches based on the content of two fields.  This post, SQL JOIN Multiple Tables with Concatenation, covers a simple example to illustrate this concept.

Example

Let’s say you have two tables:  Customer and Invoice

Your Customer table is a small list of target customers you would like to get transactional detail about.  The Invoice table contains transaction history for all customers over time.  Unfortunately, the tables you have to work with are not setup with proper keys, so the only way to bring them together is joining on two columns, FirstName and LastName.

Here is what the data looks like:

Customer Table

SQL JOIN Multiple Tables with Concatenation

Invoice Table

SQL JOIN Multiple Tables with Concatenation

What you would like to see is only the Invoice transaction detail from the Invoice table, that matches the “FirstName and LastName” from your Customer table.  Here is a look at the desired output:

SQL JOIN Multiple Tables with Concatenation

Here is the code and sample data to see how this type of JOIN is accomplished:

Please Note:  In the JOIN above, I use CONCAT function to bring the two fields together.  This will only work in SQL 2012 and above.  You can uncomment the “ON” statement below the note, to perform the same multiple field JOIN as CONCAT, for SQL 2008 and older versions.

You can modify the sample code above to suit your needs.  Enjoy!

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment