RE: SQL joining 4 files together

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Don,

Thank you.  Your explanation and some more RTFM came up with this working
solution:

SELECT login.logid, login.logpas, login.logfnm, login.logcnm,
       login.logacc, login.logact, login.loglev, login.logsts,
       login.logphone, login.logemail, login.logflg1, login.logflg2,
       login.logedte, login.logeusr, login.logmdte, login.logmusr,
       login.logpsod, login.logpwcd, login.logpwei, login.logpwex,
       tpnbr.tpnbr, tpnbr.tpttype, tpnbr.rolecode,
      tptype.tpt400, tptype.tptlib, tptype.tptfile, tptype.tptfield,
      tptype.tptpname, tptype.tpphone, tptype.tpfax, tptype.tpemail,
       tpfacl.tpfacl, tpfacl.tpdrawflr
FROM (((login left outer join tpnbr
          ON login.logid = tpnbr.logid)
      left outer join tptype
          on tpnbr.tpttype = tptype.tpttype)
      left outer join tpfacl
          on tpnbr.logid = tpfacl.logid
         and tpnbr.tpnbr = tpfacl.tpnbr
         and tpnbr.tpttype = tpfacl.tpttype)

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Fisher, Don" <Dfisher@roomstoreeast.com>
Sent by: midrange-l-admin@midrange.com
05/01/2002 02:26 PM
Please respond to midrange-l


        To:     "'midrange-l@midrange.com'" <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        RE: SQL joining 4 files together


I've never joined more than three files myself, but I believe I see a
problem.

The specification of a file before the second and third "left outer join"
clauses should probably be omitted.  When I join more than one file, I
have
never specified a file name before the other "join" clauses.  I believe,
and
I could be wrong, that the joins are assumed to relate either to the
primary
file (login in this case) or to the result set from the previous join
clauses.

Hope that helps.

Donald R. Fisher, III
Project Manager
The Roomstore Furniture Company
(804) 784-7600 extension 2124
DFisher@roomstoreeast.com

<clip>
SELECT login.logid, login.logpas, login.logfnm, login.logcnm,
       login.logacc, login.logact, login.loglev, login.logsts,
       login.logphone, login.logemail, login.logflg1, login.logflg2,
       login.logedte, login.logeusr, login.logmdte, login.logmusr,
       login.logpsod, login.logpwcd, login.logpwei, login.logpwex,
       tpnbr.tpnbr, tpnbr.tpttype, tpnbr.rolecode,
      tptype.tpt400, tptype.tptlib, tptype.tptfile, tptype.tptfield,
      tptype.tptpname, tptype.tpphone, tptype.tpfax, tptype.tpemail,
       tpfacl.tpfacl, tpfacl.tpdrawflr
FROM login left outer join tpnbr
          ON login.logid = tpnbr.logid
     tpnbr left outer join tptype
          on tpnbr.tpttype = tptype.tpttype
     tpnbr left outer join tpfacl
          on tpnbr.logid = tpfacl.logid,
         and tpnbr.tpnbr = tpfacl.tpnbr,
         and tpnbr.tpttype = tpfacl.tpttype
<clip>
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.