Okay, I admit it. Before I became an SQL Server 2000 DBA, I was bottlefed on Microsoft Access. When it came to building complex queries with joins, I became an expert on Access's Grid Manager, formally known as a form of Query By Example (QBE) developed by IBM's Dr. Zloof in the 1970's. Call me lazy but when building complex joins, I found that Access's Grid Manager was better than anything I could type or formulate so I let Access's Grid Manager do all the work. Of course, I ventured into Access's SQL View every now and then to modify the actual SQL syntax (Access's own unique version) but not too often. I didn't need to.

I found that Grid Manager's QBE afforded some welcome options:

• Two-dimensional syntax
• Queries could be expressed by example
• Closely corresponded to relational calculus (tuple and domain) where you write a single declarative expression instead of a sequence of relational algebra operations
• Non-procedural
• Skeleton tables allowed users to select and fill in with example rows consisting of constants and example elements
• Saved time
• Eliminated reference ambiguity

Figures 1 and 2 are perfect examples. Using the Northwind relational database, let's say I want to UPDATE the Quantity that was ordered (from 45 to 50) by the QUICK-stop Company on August 20, 1996 for a product called “Chai”. This would require an UPDATE query utilizing INNER JOINS between the Customers, Orders, Order Details, and Product tables. In Access, using QBE, it takes all of 30 to 60 seconds to produce the query results (see Figure 4) simply by clicking and dragging the table names and column names and then typing in the criteria (24 characters total for the Company Name, Order Date, and Product Name).

Figure 1

Figure 2

In Access's SQL View (click VIEW on the menu, then SQL VIEW).  The syntax that is generated (342 characters without spaces) is shown below (Figure 3), and "Look Ma, no hands!"

UPDATE Products 
INNER JOIN 
(
(Customers INNER JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
) 
INNER JOIN [Order Details] 
ON Orders.OrderID = [Order Details].OrderID
)
ON Products.ProductID = [Order Details].ProductID 
SET [Order Details].Quantity = 50 
WHERE (((Customers.CompanyName)="QUICK-stop") 
AND ((Orders.OrderDate)=#8/20/1996#) 
AND ((Products.ProductName)="Chai")); 

Figure 3

Imagine my surprise when I entered Microsoft's SQL Server 2000 world. Enterprise Manager's query builder was a nightmare compared to Access's Grid Manager. Whenever I needed to perform an INSERT FROM, INSERT INTO, UPDATE, or DELETE query that involved a join, the monitor flashed up a message something like this, “No! No! This can't be done with two or more tables. Let me slap your hand!” Well, maybe it wasn't exactly like that. Anyway, I thought to myself, “I actually have to know this syntax now?” Yikes! How was I to earn my salary? All kidding aside, it was a bit intimidating at first because I am dealing with a relational database that consists of seven tables. Not only are joins part of the interface design but they are also required daily for operations.

Personally, from a join perspective, I found the templates in Query Analyzer limited and the scripts to be an “all fields” only option for each table. If you are scripting tables to join together, the script has to be done individually for each table and then you have to manually build the join. Eventually, I created several templates of my own but I still wanted the lazy way to creating joins by QBE. Then it dawned on me. There was really no way that I would ever have the comfort of Access's Grid Manager. However, after reading through SQL Server Books Online, I finally derived a simple solution for building complex joins by using QBE in Enterprise Manager and mixing it up with Query Analyzer.

It is really a simple process and some of you have probably already figured it out but for those of you who have not, here goes. For example, in Enterprise Manager, open the Northwind database. Right click on any one of the four tables required to produce the same query that was used in the Access example (Customers, Orders, Order Details, or Product). Choose the Open Table option, then Query. (see Figure 4)

Figure 4

So let's start building our UPDATE query like we did in our Access example (see Figure 5).

Figure 5

Now it's time to add the other tables, BUT here comes the irritating part of QBE in Enterprise Manager! If you try to build an UPDATE query like the one in the Access example with multiple tables, you will not even have the option to add the second table. However, if you change the query to a SELECT query you can add multiple tables by simply right clicking in the Diagram pane (see Figure 6). When using Enterprise Manager's query builder, you can only add more than one table for SELECT and CREATE TABLE queries.

Figure 6

And now, the moment you have all been waiting for! Drum roll please! What exactly is the trick to using QBE in Enterprise Manager for building joins for INSERT FROM, INSERT INTO, UPDATE, or DELETE queries? First, you need to build a SELECT query with the joined tables (see Figure 7) and specify the type of join you are looking for (see Figures 8 and 9). Of course, you cannot enter the Quantity to update at this point but you can enter the criteria (CustomerID, OrderDate, and

ProductName).

Figure 7

A handy piece of information to know when using Enterprise Manager's query builder for joins is that you can change the type of join by right clicking your mouse on the join icon connected between the tables (see Figure 8). For more detail, click on Properties on the Join menu, to change the join properties (see Figure 9). Note: These examples are not using the Northwind database.

Now, all you have to do is copy and paste the code into Query Analyzer (Figure 10) because you now have the basis for the INSERT FROM, INSERT INTO, UPDATE, or DELETE queries with this particular join. All you have to do is modify the code to your needs.

Figure 10

To modify the code for the UPDATE query, it's quite simple. You just need to delete the SELECT statement, add the UPDATE statement, and save the FROM and WHERE part of the query (see Figure 11). The best part is that the referential integrity is in tact for the join and it took maybe a minute to create the code.

Figure 11

Another example would be creating an INSERT INTO/INSERT FROM query with this join. Let's say we want to insert the CustomerID, OrderDate, Quantity, and ProductName into an existing file (i.e., CustomerArchive). It's simple. Just save the body of the SELECT query and add the INSERT INTO statement.

Figure 12

Conclusion

There are some benefits to using Enterprise Manager's query builder when working with joins. It definitely saves time, seriously decreases typographical errors, allows you to visualize the join, and enables referential integrity. My personal wish is that Microsoft would improve the QBE interface in Enterprise Manager to the level of QBE provided in Microsoft Access's Grid Manager. Microsoft seems to provide product-segmented levels of support for these two products. However, it would be nice if the Access developers would cough up the QBE interface code to the SQL Server developers. In the meantime, I hope that these QBE tips will help you save time in your workplace.