Create a Self-Join in a Query Manually

Background information

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 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

Create a self-join manually

In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
Add to the Diagram pane the table, view, or function you want to work with.
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.
Add the same table again, so that the Diagram pane shows the same table, view, or function twice within the Diagram pane.
The Query Designer assigns an alias to the second instance by adding a sequential number to the table name. In addition, the Query Designer creates a join line between the two occurrences of the table, view, or function within the Diagram pane.
Right-click the join line, choose Properties from the shortcut menu, and then 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, view, or function 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.