Re: another can this be done with sql

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

----- Original Message -----
From: "Joe Pluta" <joepluta@PlutaBrothers.com>
To: <midrange-l@midrange.com>
Sent: Friday, February 01, 2002 11:05 AM
Subject: RE: another can this be done with sql


> > select * from sofile s
> > join acctfile on soact = acctact  a
> > join codefile c on s.sostat = c.stat and a.acctstat = c.acctstat and
> > s.sotype = c.sotype
>
> A couple of quick questions on this just to show my SQL ignorance: do the
> acctfile join and codefile join act in tandem (ANDed) or separately
(ORed)?
> I ask because you said breaking up the code portion would cause it to be
> ORed.  I'm wondering why.  Is the above statement ANDed because a field
from
> acctfile is used in the join to codefile?

or might not have been the right word...
The third join would look for a record that contains a particular
combination of stat _and_ acctstat _and_ sotype.
The files would look for any valid stat and then any valid acctstat and then
any valid sotype.
It would result in a much more inclusive join.

> Second, could multiple type files could be handled like this:
>
> select * from sofile s
> join acctfile on soact = acctact  a
> where s.sostat in (select x.sostat from SOSTATS x)
> and a.acctstat in (select y.acctstat from ACCTSTATS y)
> and s.sotype in (select z.sotype from SOTYPES z)
>

Sure, but that builds the any/or that I mentioned above as well. Now the and
intention is somewhat obscured, and the combination of codes is not honored.


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"Suppose you were an idiot...
  And suppose you were a member of Congress...
  But I repeat myself."
    - Mark Twain