× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.