RE: Joining to the same file twice

No, it's not, but I didn't want to make everybody's hair hurt this early in
the week.  But thanks for your solution...oops, it's David Gibbs's solution
now!

-reeve

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Monday, November 17, 2003 9:54 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: Joining to the same file twice
> 
> That really isn't your statement, is it Reeve?
> 
> CREATE TABLE ROB/MASTER (EMPNO CHAR (10 ) NOT NULL WITH DEFAULT,
> EMPNAME CHAR (25 ) NOT NULL WITH DEFAULT, CONSTRAINT EMPNO PRIMARY
> KEY (EMPNO))
> 
> INSERT INTO ROB/MASTER VALUES('00001', 'Bubba')
> INSERT INTO ROB/MASTER VALUES('00002', 'Jimmy')
> 
> CREATE TABLE ROB/ORDERS (ORDER# DEC (7 , 0) NOT NULL WITH DEFAULT,
> ACCOUNT DEC (7 , 0) NOT NULL WITH DEFAULT, SALES# CHAR (10 ) NOT
> NULL WITH DEFAULT, SERVICE# CHAR (10 ) NOT NULL WITH DEFAULT,
> CONSTRAINT SALES# FOREIGN KEY (SALES#) REFERENCES ROB/MASTER
> (EMPNO) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT SERVICE#
> FOREIGN KEY (SERVICE#) REFERENCES ROB/MASTER (EMPNO) ON DELETE
> RESTRICT ON UPDATE RESTRICT, CONSTRAINT ORDER# PRIMARY KEY (ORDER#))
> 
> INSERT INTO ROB/ORDERS VALUES(123, 456, '00001', '00002')
> 
> select account, a.empname as SalesGuy, b.empname as ServiceGuy
> from orders
>  left outer join master a on orders.sales#=a.empno
>  left outer join master b on orders.service#=b.empno
> 
> 
> ACCOUNT   SALESGUY                   SERVICEGUY
>     456   Bubba                      Jimmy
> 
> I think you are missing the a and b.
> 
> Rob Berendt
> --
> "They that can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety."
> Benjamin Franklin
> 
> 
> 
> 
> "Reeve Fritchman" <gp3dad@xxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 11/16/2003 08:23 PM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> 
> To
> midrange-l@xxxxxxxxxxxx
> cc
> 
> Subject
> Joining to the same file twice
> 
> 
> 
> 
> 
> 
> I'm trying to join to a master file two times in the same record (once for
> 
> the sale rep's name; one for the tech's name).  The application will be
> embedded SQL in an ILE RPG program.
> 
> I can solve the problem by creating a logical with renamed fields :( but
> I'm
> sure I've seen a way that doesn't require a external solution.
> 
> This way (below) works but SQL tells me my subquery has an unqualified
> correlation.  I don't know if that means the Programming Police will be
> after me or if it's an indication of a potential performance problem.  I
> could write a stored procedure but I think that's overkill.
> 
> select account,name,
> (select sales    from master where empno=sales#),
> (select service from master where empno=service#) from orders
> 
> thanks---
> 
> _________________________________________________________________
> MSN Messenger with backgrounds, emoticons and more.
> http://www.msnmessenger-download.com/tracking/cdp_customize
> 
> _______________________________________________
> 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.