How to: Create Self-Joins Manually
You can join a table to itself even if the table does not have a reflexive relationship in the database. For example, you can use a self-join to find pairs of authors living in the same city.
As with any join, a self-join requires at least two tables. The difference is that, instead of adding a second table to the query, you add a second instance of the same table. That way, you can compare a column in the first instance of the table to the same column in the second instance, which allows you to compare the values in a column to each other. The Query and View Designer assigns an alias to the second instance of the table.
For example, if you are creating a self-join to find all pairs of authors within Berkeley, you compare the city column in the first instance of the table against the city column in the second instance. The resulting query might look like the following:
SELECT
authors.au_fname,
authors.au_lname,
authors1.au_fname AS Expr2,
authors1.au_lname AS Expr3
FROM
authors
INNER JOIN
authors authors1
ON authors.city
= authors1.city
WHERE
authors.city = 'Berkeley'
Creating a self-join often requires multiple join conditions. To understand why, consider the result of the preceding query:
Cheryl Carson Cheryl Carson
Abraham Bennet Abraham Bennet
Cheryl Carson Abraham Bennet
Abraham Bennet Cheryl Carson
The first row is useless; it indicates that Cheryl Carson lives in the same city as Cheryl Carson. The second row is equally useless. To eliminate this useless data, you add another condition retaining only those result rows in which the two author names describe different authors. The resulting query might look like this:
SELECT
authors.au_fname,
authors.au_lname,
authors1.au_fname AS Expr2,
authors1.au_lname AS Expr3
FROM
authors
INNER JOIN
authors authors1
ON authors.city
= authors1.city
AND authors.au_id
<> authors1.au_id
WHERE
authors.city = 'Berkeley'
The result set is improved:
Cheryl Carson Abraham Bennet
Abraham Bennet Cheryl Carson
But the two result rows are redundant. The first says Carson lives in the same city as Bennet, and the second says the Bennet lives in the same city as Carson. To eliminate this redundancy, you can alter the second join condition from "not equals" to "less than." The resulting query might look like this:
SELECT
authors.au_fname,
authors.au_lname,
authors1.au_fname AS Expr2,
authors1.au_lname AS Expr3
FROM
authors
INNER JOIN
authors authors1
ON authors.city
= authors1.city
AND authors.au_id
< authors1.au_id
WHERE
authors.city = 'Berkeley'
And the result set looks like this:
Cheryl Carson Abraham Bennet
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.
To create a self-join manually
Add to the Diagram pane the table or table-valued object you want to work with.
Add the same table again, so that the Diagram pane shows the same table or table-valued object twice within the Diagram pane.
The Query and View Designer assigns an alias to the second instance by adding a sequential number to the table name. In addition, the Query and View Designer creates a join line between the two occurrences of the table or table-valued object within the Diagram pane.
Right-click the join line and choose Properties from the shortcut menu.
In the Properties window click Join Condition and Type and click the ellipses (…) to the right of the property.
In the Join dialog box change the comparison operator between the primary keys as required. For example, you might change the operator to less than (<).
Create the additional join condition (for example, authors.zip = authors1.zip) by dragging the name of the primary join column in the first occurrence of the table or table-valued object and dropping it on the corresponding column in the second occurrence.
Specify other options for the query such as output columns, search conditions, and sort order.
See Also
Tasks
How to: Create Self-Joins Automatically