Monday, April 11, 2005


Development 4/11/05 - Joins Be Gone

I finally added a feature I have had on my personal want list for some time but never got around to it. While I was testing some SCC stuff this weekend and was waiting for it to finish I decided to spec out the feature and was able to get it into the upcoming v2.16 release.

For those of you who currently use the "standard" join syntax of "table join table on", you're life just got much easier. For those of you who still use the legacy join syntax (yes, that's what it's referred to and I do know some of you :), of "from table, table where table.col = table.col", you no longer have any excuse.

With v2.16, after you type "join", the Intellilist that is displayed will contain some new items when there are FK constraints that reference any table in the current query. Lookin at the Northwind database, if I type:

"select * from Orders inner join"

The list that is displayed now has some new entries at the top immediately following any schema:

- Join Customers (FK_Order_Customers)
- Join Employees (FK_Order_Employees)
- Join Order Details (FK_Order_Details_Order)
- Join Shippers (FK_Order_Shippers)

Notice that the options include both FKs that the current table has (Customer, Employee, Shipper) plus tables that reference the current table of Orders (Order Details). Picking any of these options will insert not only the specified table but the join condition as well (including any multi-col join conditions).

So, picking the - Join Customers ... option would then make my query look as follows:

select * from Ordeers inner join Customers on Customers.CustomerID = Orders.CustomerID

Do another inner join and you will get even more options, with the joins from Customer now included in the list. The joins are displayed from the most recent table to the least recent, so joins to Customer would be at the top and joins to Orders would be below these and so forth.

What about aliases, eh. Well, if you defined the table with an alias then the join condition will use that alias. Also, if you have predefined aliases for objects, then the join table and condition inserted will use those predefined aliases as well.

In my opinion, this is the best thing since the soup and sandwich combo at Panera Bread (I'm on my way there shortly :)

We will also be providing the ability to define custom joins in a future release so that the user can manually define join conditions that might exist outside of the normal foreign key relationships defined in the database.

The inserted tables will also follow the "Insert Object Owner" setting and insert the table schema for the new table if turned on.
Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?