Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

Join Creation



Equipped with the necessary tables and their columns, you can create the join. To do this in the SQL Server Management Studio, you can right-click one of the tables involved and click Open Table. This would display the Table window. You should then display the Diagram and the SQL sections. Because the foundation of a join lies on at least two tables, you should add one. To do this

On the main menu, you can click Query Designer -> Add Table
On the Query Designer toolbar, you can click the Add Table button
You can right-click the Diagram section of the window and click Add table...
Any of these actions would display the Add Table dialog box. To select a table:

You can click the table's name and click Add
You can double-click a table
Alternatively, instead of using Add Table, you can drag the child table from the Object Explorer and drop it in the Diagram section.

Here is an example of two tables that have been added:


Remember that you can drag the title bars of the tables to move them and position them to your liking. After selecting the table(s), on the Add Table dialog box, you can click Close.

If a relationship was already established between the tables, a joining line would show it.

In SQL code, the basic formula to create a join is:

SELECT WhatColumn(s)
FROM ChildTable
TypeOfJoin ParentTable
ON Condition
The ChildTable factor specifies the table that holds the records that will be retrieved. It can be represented as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin ParentTable
ON Condition
The ParentTable factor specifies the table that holds the column with the primary key that will control what records, related to the child table, that will display. This factor would be represented as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID
The Condition factor is a logical expression used to validate the records that will be isolated. To create the condition, you should assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID
The WhatColumn(s) factor of our formula allows you to make a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:

SELECT *
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID
In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:

SELECT LastName, FirstName, Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID
If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name. Here is an example:

SELECT LastName, FirstName, Persons.GenderID,
Genders.GenderID, Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID
In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here is an example:

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
Genders.GenderID, Genders.Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID
Practical Learning: Preparing a Join



In the Object Explorer, right-click Databases and click Refresh
Expand Databases and expand RealEstate2
Expand its Tables node
Right-click the Properties table and click Open Table
On the Query Designer toolbar, click the Show Diagram Pane button and the Show SQL Pane button
On the Query Design toolbar, click the Add Table button
In the Add Table dialog box, double-click PropertyTypes
Click Close



source : http://blogs.msdn.com/sharepoint/default.aspx