RE: next baby step in SQL: start using join

["Chevalier, Rick" <Rick.Chevalier@xxxxxxxxxxxxxxx>]

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.