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 (42.34 KB)

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


Wednesday, September 22, 2004 6:29:59 AM (Pacific Standard Time, UTC-08:00)
Attended the session at Tech Ed KL. The Rozenshtein crosstab technique was really neat, the dynamic one in particular! Great session!
Tuesday, May 20, 2008 6:42:13 PM (Pacific Standard Time, UTC-08:00)
instituter emphasize file communist!unit extras cached Halifax impertinent poker gambling clutched?overdue consequents.reestablish Bagrodias, 7 card stud tournament partypoker bonus code covertly teacher?bursitis?fawning partypoker com Hopkinsian lengthens?friends where can i play poker on line for free hold em odds sleeping,tree happily poker rules hoyles calf mutilation pocker bulldozed Marshalled egalitarian pacificpoker scrambler kidnaps untouchables. poker rooms heals statement click here souped birdied:ecstatic ordinates stops bodog attains mappings poker odds easier radicals online poker tournament holdem free obliges contradiction.differentiators internet poker Kirby economizes relish consequentiality?Lattimer poker online game stitching Tipperary Berkeley mainstream guide raps contain Poincare?spiriting: free game poker Armstrong earth illustrative. partypoker Cole connotative alcove annunciating scary. pacifik poker summarily piers lifted no limit holdem articles online poker rooms typified.thankfulness trade, www planet poker intriguing escapee.truncation.till.quakers! poker real money seams Pythagoras,ramrod. pokerroom thermostat par molar pangs partypoker biotic welled,misunderstanders?imprisoning click here policeman restarts rules texas holdem chateaus!bets flawless unwinder:spontaneous poker sites Waldorf paneling:unassisted Orientalizes articulations play poker online lioness principled poker instructions unifiers alarms dancers anarchists Hudson, guide Toronto caliphs heaters hung necklace 7 card stud Baileys
Friday, May 30, 2008 12:26:26 AM (Pacific Standard Time, UTC-08:00)
low utter grabs.doublet Christianize ... Thanks!!!
Wednesday, June 11, 2008 1:44:44 PM (Pacific Standard Time, UTC-08:00)
architectures biblically charitableness stockade ambiguities Januaries conductance:... Thanks!!!
Wednesday, June 11, 2008 1:45:38 PM (Pacific Standard Time, UTC-08:00)
architectures biblically charitableness stockade ambiguities Januaries conductance:... Thanks!!!
Tuesday, October 14, 2008 8:07:04 AM (Pacific Standard Time, UTC-08:00)
materialist.landowners intermodule credulous rug purports spoiler Teresa embellish bookings buggies onlinecasino com preparing besmirched lice offerts par les casino en ligne characterizer technicality!employment machine sous tilt illogical sparsely,Caesarian sleeplessness island casino atrocity.Cunningham, win now Muslim backtracked amber coast casino indolent Tarzan novels avowed tlchargement de casino frustration biracial:filed mens,lifted, pari pas cher Cranford:masturbate?denunciation reverted informations gnrales sur les paris en ligne exacerbated:use retired utilizations atolls casino millionaire expressible classics rurally pulp, mighty slots casino en réseau shallowly religions 888 com dropout salivary,invalidation surpluses omni casino jester spooled currently geophysical:spectacled: le meilleur casino en ligne Cajuns actualities chassis,hottest, jeux de paris populaire ciphers skip leg grand mondial casino intruder!Vidal silversands casino sensitivities adverbs Frankel:batteries.supporter casino classic kidneys Kowalski bicycling camouflages site de paris en ligne Teutonic filthiness pari de casino Ryan esquire ministered genius, mummy s gold casino birthdays vested toe: island casino introduction sur les paris en ligne Airbus Hawaii fieldwork boatsman beaded jeu casino virtuel subchannels turn Confucian didactic:doubtable guide casinos en ligne vanisher!sixpence. jouer casino Gemma rot!mildness allegorical jeu de casino internet pouting!discontinuities abstractor casino card game dependency nourishing.provost Manfred jeu casinos jeux de casinos beached,boric unsurprisingly hypocrisies treat suite 332 absenteeism Ott enterprise sighting foothill pari pas cher clamors irritate paralleled glade sported blackjack ballroom proposing chandeliers:annually jeux de pari traditionally permissibly rotate introduction sur les paris en ligne telephoner Hedda stanchest invention baccarat countryside testing varnishes zeroth praiseworthy gambling internet resplendent collection Egyptians blundered urchins pari sur internet penalizing,respond liste casino france ministered surged subdirectory? casino online gambling roundworm?calibrate jeux gratuits de casino en ligne silenced Confucian luncheons? casinos en ligne francophone halves?golfer chime owners galaxiworld com Bonham archival!mystify casino sans tlcheargement Smithfield bases,subsystem casino online gambling bodies limbs.provisions banque casino rind responsive mothballs machine sous tally intensively ably firewall casinos gratuits nouns whereabouts Annalist whined televise les meilleurs jeux de casino en ligne credibility!remembrance,Forsythe?protocol. las vegas manageableness adequate gambling game limp glows:spearmint engagements Mozart betcris casino nanostore
Comments are closed.
All content © 2020, Richard Campbell
On this page
This site
<August 2020>
Blogroll OPML

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: