Thanks bill with a few adjustments that worked great and knocked of about 75 false positives I got by not including the user table.
on 8/7/03 22:53, Bill Downall at [EMAIL PROTECTED] wrote: > On Thu, 07 Aug 2003 20:37:41 -0400, Dan Stein wrote: > >> So in the end I want all the people who are not in the user table but also >> don't have an email address in the registration table and have taken an >> aquatics course. > > Dan, > > Something like: > > SELECT regid, address, (and whatever else) > FROM Registration r1 > WHERE emailcolumn is null > AND regid IN > (SELECT regid FROM LineItems i2 > WHERE something = 'swim' AND i2.regid = r1.regid) > AND regid NOT IN > ( SELECT regid FROM User u3 WHERE u3.regid = r1.regid ) > > Depending on how smart the query optimizer is in MS SQL, the subqueries > may also work just as well or better without the correlated subselect: > > SELECT regid, address, (and whatever else) > FROM Registration r1 > WHERE emailcolumn is null > AND regid IN > (SELECT regid FROM LineItems i2 > WHERE something = 'swim' ) > AND regid NOT IN > ( SELECT regid FROM User u3 ) > > Bill > > > > > > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > -- Dan Stein Digital Software Solutions 799 Evergreen Circle Telford PA 18969 Land: 215-799-0192 Mobile: 610-256-2843 Fax 413-410-9682 FMP, WiTango, EDI,SQL 2000 [EMAIL PROTECTED] www.dss-db.com ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
