[thelist] ASP, SQL, Recordsets, Best Practices

[rudy]

>Wouldn't this be quicker:
>
>select 1 from [table] where [uniqueness]
>
>No functions... just a literal.
>
>If I recall, I learned this from you...

hi matt

you honour me and it is appreciated

there is a subtle difference in the two queries

select count(*) will always return exactly one row with one value, zero or
positive

select 1 will only return a 1 if a row exists, otherwise it will return a
null, and let's just not go there, okay?

and don't forget that without the uniqueness test, select 1 will return as
many 1s as rows  ;o)

the situation that select 1 usually comes up in, therefore, is in a
coordinated subselect where you want to know existence only

for example

     select username, userdata
         from usertable
      where not exists
          (select 1
              from nosoupforyou
           where userid = usertable.userid)

this query gets all the users that don't have an entry in the nosoupforyou
table

in the case of the subquery, you don't really need to return anything,
because it's a true/false condition

even if the subquery potentially might return more than one row (e.g. you
designed the nosoupforyou table to record multiple transgressions by the
same individual), the database would still probably resolve the subquery
after finding the first row and not bother getting them all

you could also do the subselect with count(*) --

     select username, userdata
         from usertable
      where 0 =
          (select count(*)
              from nosoupforyou
           where userid = usertable.userid)

in this case the database might -- you never know how clever the path
optimizers are these days -- actually go out and count them all before
evaluating the 0= test

see the difference?

since michele wants also to use the single record's data fields, she won't
be using a subselect, rather, she wants the data fields returned in the
select

she can therefore either test for a null in the primary key of the returned
record, or else go through all that programming rigamarole that asp
developers are so fond of...     <dons flame-retardant underwear again />


>if objRS.eof and objRS.bof then
>    bSuccess = Dexter.stopUsingFrontPage()
>else
>    bSuccess = Rudeman.learnRealSQL()
>end if
>
>And, of course, bSuccess will always be false in both cases...

good one   ;o)

unfortunately "real" sql is a standard (sql-99, i think) with which i am
not terribly familiar

standard sql suffers from even more bastardizations by proprietary vendor
extensions than html

i only know specific manifestations of sql, as delivered unto us by the
database vendors in their endless benevolence...


rudy

p.s. i know this wasn't asked, but one final comment on the count(*)
transgressions query

notice that this is obviously the one you would need if it mattered how
many transgressions...  for example, to get all users that have fewer than
three strikes against them --

      where 3 >
          (select count(*)
              from nosoupforyou
           where userid = usertable.userid)