Create a Join Between Tables on Multiple Columns in a Query

You can join tables with multiple columns. That is, you can create a query that matches rows from the two tables only if they satisfy multiple conditions. If the database contains a relationship matching multiple foreign-key columns in one table to a multicolumn primary key in the other table, you can use this relationship to create a multicolumn join.

Even if the database contains no multi-column foreign-key relationship, you can create the join manually.

1. In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
2. Add to the Diagram pane the tables you want to join.

   How?

   When you create a query, you are retrieving data from a table, view, or function. To work with any of these objects in your query, you add them to the Diagram pane.

   Add a table, view, or user-defined function to the query

   1. In the Database window, click Queries   under Objects, click the query you want to open, and then click Design on the database window toolbar.
   2. In the Diagram pane, right-click the background and choose Add Table from the shortcut menu.
   3. In the Add Table dialog box, select the Tables, Views, or Functions tab.
   4. In the list of items, double-click each item you want to add.
   5. When you finish adding items, click Close.

   The Query Designer updates the Diagram pane, Grid pane, and SQL pane accordingly.

   Alternatively, you can drag objects onto the Diagram pane. You can drag a table, view, or inline function from the database window.

   You can also drag columns or tables from the Database Designer or paste them from the Clipboard.

   Tables and views are automatically added to the query when you reference them in the statement in the SQL pane.

   The Query Designer will not display data columns for an table, view, or inline function if you do not have sufficient access rights. In such cases, only a title bar and the * (All Columns) check box are displayed for the table, view, or inline function.

   Add an existing query to a new query

   1. If necessary, click SQL to show the SQL pane.
   2. In the SQL pane, type a right and left parentheses () after the word FROM.
   3. Open the Query designer for the existing query . (You now have two Query Designers open.)
   4. Display the SQL pane for the inner query – the existing query you are including in the new, outer query.
   5. Select all the text in the SQL pane, and copy it to the Clipboard.
   6. Click in the SQL pane of the new query, situate the cursor between the parentheses you added, and paste the contents of the Clipboard.
   7. Still in the SQL pane, add an alias after the right parenthesis. For more information on SQL aliases and subqueries, see the Microsoft SQL Server documentation.
3. Drag the name of the first join column in the first table window and drop it onto the related column in the second table window. You cannot base a join on text, ntext, or image columns.

   Note   In general, the join columns must be of the same (or compatible) data types. For example, if the join column in the first table is a date, you must relate it to a date column in the second table. On the other hand, if the first join column is an integer, the related join column must also be of an integer data type, but it can be a different size. However, Microsoft SQL Server provides implicit data type conversions so that many joins between seemingly incompatible columns will work.

   The Query Designer will not check the data types of the columns you use to create a join, but when you execute the query, the database will display an error if the data types are not compatible.

4. Drag the name of the second join column in the first table window and drop it onto the related column in the second table window.