Re: Need some SQL DB Help

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Rob, your last example, using the old JOIN technique of listing tables, 
omits the WHERE clause that specifies the JOIN criteria. Put the ON stuff 
from the previous statement (ftpuser.company = ftpcompany.company) in a 
WHERE clause and the results should be right.

I think!
Vern

At 11:35 AM 5/4/2005, you wrote:

> Define "works".
>
> For example if I do this:
> select substr(login,1,20) as UserId,
>        company
> from gdweb/ftpuser
>
> I get this:
>
> ....+....1....+....2....+....
> USERID                Initial
>                       Company
> RALPH                   01
> JONI                    02
> POTSIE                  01
> ********  End of data  ********
>
> And if I do:
> Select company,
>        library
> from gdweb/ftpcompany
>
> I get this:
> ....+....1....+.
> Company  Default
> Number   Library
>   01     -
>   02     ROB
>
> And if I do it the way you're used to:
> select substr(ftpuser.login,1,20) as UserId,
>        ftpuser.company,
>        ftpcompany.library
> from gdweb/ftpuser
>      join gdweb/ftpcompany on ftpuser.company = ftpcompany.company
>
> I get this:
> ....+....1....+....2....+....3....+...
> USERID                Initial  Default
>                       Company  Library
> RALPH                   01     -
> POTSIE                  01     -
> JONI                    02     ROB
>
> However, if I try their technique:
> select substr(ftpuser.login,1,20) as UserId,
>        ftpuser.company,
>        ftpcompany.library
> from gdweb/ftpuser, gdweb/ftpcompany
> I get this:
> ....+....1....+....2....+....3....+...
> USERID                Initial  Default
>                       Company  Library
> RALPH                   01     -
> RALPH                   01     ROB
> JONI                    02     -
> JONI                    02     ROB
> POTSIE                  01     -
> POTSIE                  01     ROB
>
> Which works, but isn't right.
>
> And there's even a foreign key constraint between FTPUSER and FTPCOMPANY
> based on COMPANY.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> Mike Wills <koldark@xxxxxxxxx>
> Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
> 05/04/2005 11:06 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
>
> Subject
> Need some SQL DB Help
>
>
>
>
>
>
> I am hoping someone can help me out here. We have an SQL collection from a
>
> vendor that has different constraints(? joins that are predefined). I am
> trying to figure out how one of these joins works can for the life of me
> can't figure it out. The documentation doesn't show any join, but yet when
> I
> run this:
>
> select * from dbpopcr, dbposcm
>
> it works. Is there a way I can figure out this join using IBM delivered
> tools?
>
> --
> Mike Wills
> Midrange Programmer/Lawson Software Administrator
> koldark@xxxxxxxxx
> http://mikewills.name
> Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.