Re: Joining to the same file twice

[rob@xxxxxxxxx]

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.