× 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.



Dan,

Try this:

>From PQPROD/PESTCCP T01 join  PQPROD/PJOBHDR T03 on T01.JOBNUM = T03.JOBNUM
        Join PQPROD/PTBLCCP T02 on T03.CMPNUM = T02.CMPNUM and T01.CCPTYP =
T02.TBLID
        And T01.CCPNUM = T02.CCPNUM
Where T01.JOBNUM = 'TACM0170'   AND     T02.CSTTYP = 'C'

You will improve the performance if you can arrange the join to place the
most restrictive join first and move to the least restrictive join.

HTH,

Rick

-----Original Message-----
From: Dan [mailto:dbcemid@xxxxxxxxx]
Sent: Thursday, March 27, 2003 4:28 PM
To: Midrange Systems Technical Discussion
Subject: Re: next baby step in SQL: start using join

Thanks for all the replies so far.  I get the gist of it now.

O.k., I pulled out the SQL reference and see it talking about all the joins,
but what I don't see
is any reference to the type of join I'm trying to do.  I believe it is
basically an Inner join,
only want to get back rows for which data exists in both files.  However, I
have a nasty twist to
this.  Referring back to the SQL:
 FROM      PQPROD/PESTCCP T01,
           PQPROD/PTBLCCP T02,
           PQPROD/PJOBHDR T03 
 WHERE     T03.CMPNUM = T02.CMPNUM
   AND     T01.CCPTYP = T02.TBLID 
   AND     T01.CCPNUM = T02.CCPNUM
   AND     T01.JOBNUM = T03.JOBNUM
   AND(    T01.JOBNUM = 'TACM0170'
   AND     T02.CSTTYP = 'C')    

T01 is the big table, and I need to go after the T02 row using Company#,
Table ID, and CCP#.
Unfortunately, the Company# doesn't exist in the T01 table, so I have to use
the Job# in T01 to
join T03 in order to get the Company#.  (Believe me, I understand that the
database design on this
is lacking, but, essentially, the Job# is *ALWAYS* unique, even across
different company numbers.)
 So, it would appear that there are two joins to do here, but I have no clue
how to accomplish it.
 Any help here?  Even pointers to a specific FM reference would be
appreciated.

TIA, Dan

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
_______________________________________________
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 ...


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.