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, 20 September 2004 04:03:01 (Pacific Standard Time, UTC-08:00) #    Comments [6]  | 

 

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