Joining Tables on Multiple Columns
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. For background information, see Join Columns. 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. For details, see Joining Tables Automatically.
Even if the database contains no multi-column foreign-key relationship, you can create the join manually.
To manually create a multicolumn join
- Add to the Diagram pane the tables you want to join.
- 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, 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. - 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.
- Repeat step 3 for each additional pair of join columns in the two tables.
See Also
Joining Tables Automatically | Joining Tables Manually | Querying Using Multiple Tables