Creating a Database Project in VS 2005 - Creating a new query (Page 3 of 4 )
Highlight the Queries node and right click to reveal the drop-down. From the drop down choose Add New Item... . You may also add an existing script, add a query, and so on. For now, we will be creating a query for the referenced database.
This opens up the templates window, Add New Item, with Database query in the Visual Studio installed templates collection. The default is Query1.dtq; we can change it to something different, details.dtq for example, and click on the button Add.
Several things happen immediately. Your Project explorer has the details.dtq added to the Queries folder as shown.
The Query Editor window with four panes, all empty, will be open.
The Add Table window will be in view with all the tables available from the referenced database, as shown in the next picture. In the Add Table dialog you can hold down either the Shift Key or the Ctrl key and choose multiple tables to be sent to the design pane. Here three tables have been highlighted; they will be used in the design of the query.
When you click on the Add button in the above window, the selected tables will appear in the design pane of the editor as shown, and a minimal (but incomplete) SQL statement will be generated, showing the joins made based on existing relationships. At this point no columns have been included in the query (right now it is not a query, but a template). You may now close the Add Table window.
SELECT
FROM (([Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID) INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID)
The many-to-many relationship between Orders and Products are related to each other through the Order-Details table, which has a one-to-many relationship with both tables. Let's now pick up a few columns to be added to the query: OrderID, OrderDate, UnitPrice, Quantity, and ProductName from the three tables. We do this by placing a check mark in the check boxes by the side of the column names in each of these tables as shown. Automatically the Criteria pane changes, and the SQL statement takes in all those columns that were added with proper table reference as shown.
Next: The Query Editor >>
More Database Articles
More By Jayaram Krishnaswamy
/ 10 
