RE: Joining to the same file twice

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Reeve,

You can do this easily by using the formal join syntax:

Select a.cust, a.custname, a.slm#1, b.slmName, a.slm#2, c.slmName
From                            Customer a
        left outer join Salesman b on (a.slm#1 = b.slmId)
        left outer join salesman c on (a.slm#2 = c.slmId)



Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Reeve Fritchman [mailto:gp3dad@xxxxxxxxxxx]
Sent: Sunday, November 16, 2003 7:24 PM
To: midrange-l@xxxxxxxxxxxx
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.