Advanced Querying Techniques#

This session is one of my favorite “grab-bag” type sessions, and judging by the reaction of the attendees, one of their favorites too. I've never been mobbed with USB keys at the end of a session before. For those of you who didn't grab the code immediately off of me after the session, or from Tech Ed's COMM Net, I'm including it here as well.

In the session I explored essentially four areas of querying techniques. To some degree, they built on each other.

Initially I explored subquerying, an area lots of people dabble in, but haven't really hammered out all the details on. My personal favorite of the subquery examples is a duplicate detection query. In the real world, duplicate data gets entered, and we have to go clean it up. This query returns rows for every duplicate product, showing the oldest ID as the “real” one as well as the duplicate. You can adapt this query to clean up your database, transferring any foreign key rows to the oldest ID, etc.

SELECT Product_ID, Product_Name, Price,
 (SELECT MIN(Product_ID) FROM Products AS P1
  WHERE Products.Product_Name = P1.Product_Name) AS OldID
FROM Products WHERE Product_ID NOT IN
 (SELECT MIN(Product_ID) FROM Products AS P2
  WHERE Products.Product_Name = P2.Product_Name)

This version of the query show both a correlated subquery in the SELECT clause and the WHERE clause, as well as doing that one trick that subqueries are so good at - finding exceptions. The NOT IN statement says “give me only those rows that are not one of the minimum IDs for that product name.“

While I'm not going to go through every technique in the session, attached is the sample code for all of the techniques, including the very popular Rozenshtein cross-tab technique - both static and dynamic.

Advanced Querying.zip (42.34 KB)

Monday, September 20, 2004 4:03:01 AM (Pacific Standard Time, UTC-08:00) #    Comments [6]  | 

 

All content © 2019, Richard Campbell
On this page
This site
Calendar
<June 2019>
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456
Archives
Sitemap
Blogroll OPML
Disclaimer

Powered by: newtelligence dasBlog 1.9.7067.0

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

Send mail to the author(s) E-mail

Theme design by Jelle Druyts


Pick a theme: