[thelist] More ASP/database questions...

rudy r937 at interlog.com
Wed Apr 18 15:26:30 CDT 2001


>>     where tableB.categoryID = tableA.categoryID
>
>That's yer join =)


y'all are gonna hate me for splitting hairs here.....

actually,

       from TableA, Table B

is the join

the WHERE clause simply qualifies which combined rows you want

most people think it's obvious that you should only want fred's row from
TableA together with fred's row from TableB -- i mean, it just seems
obvious, right?

well, unless you tell the database otherwise, you get all possible
combinations of rows from TableA and TableB

hence, the WHERE clause to eliminate all but the desired rows

      where tableB.categoryID = tableA.categoryID

usually, this is referred to as the "join condition" while other portions
of the where clause, if any, are called "filter conditions"

tab's question was a good one, though --

> Umm... don't you need a JOIN in there somewhere?

this was no doubt prompted by being more familiar with the standard join
syntax, which looks like this --

   select  stuff
     from tableA join tableB
         on tableB.categoryID = tableA.categoryID

here the join condition is actually coded as such, using the ON keyword,
and not included in the WHERE clause

by the way, in case anybody's curious, are there ever situations where you
do *not* want a join condition, i.e. where the full cartesian product of
each row from TableA matched to every row from TableB is desired?

yes

here's an example used to generate test data for each salesperson for each
month

    create table months (mth char(3));
    insert into months  values ('Jan');
    insert into months  values (Feb);
      etc.

now just join this table to your salesperson table

    insert into testdata
      select salespersonid, mth, foo(bar)
         from salesperson, months

note no join condition   =o)

foo(bar) is some unspecified function, not germane to the example, which
generates a test data number (e.g. take the 5th through 7th characters of
the salesperson's name, convert to bit string, convert to binary, then cube
it and take the square root...  or something wacky like that....)


rudy.ca






More information about the thelist mailing list