Self Join

marathonman · #1 (**permalink**) 11-24-09, 13:31

Hi, I was wondering if someone can shed some light on this, not sure if a self join is the solution:

I have the following table, lets call it TEC.PARTSDATA :

Part Number, Part Desc, Fwd To, Fwd Part Number, Vendor
4321, XYZ, Y, 3456, TRWS
2112, FTYH, N, ‘’, TRWS,
1234, ZYZ, N, ‘’, RTYF,
3456, SDE, N, ‘’, TRWS,
9687, EWE, Y, 0987, TRWS,
8888, YYR, N, ‘’, RRRR,
0987, EEE, N, ‘’, TRWS,
7777, TYT, Y, 0495, ETYU,
0495, RRQ, N, ‘’, ETYU

Possible using a self join and where Vendor = TRWS, I would like to get the following result set:

Part Number, Part Desc, Fwd To, Fwd Part Number, Part Desc
4321, XYZ, Y, 3456, SDE
2112, FTYH, N, ‘’, ‘’,
3456, SDE, N, ‘’, ‘’,
9687, EWE, Y, 0987 EEE,

ARWinner · #2 (**permalink**) 11-24-09, 14:03

If you call joining a table to itself a self-join, then that is what you need. Something like

Code:

select t1.PartNumber, t1.PartDesc, t1.FwdTo, t2.PartNumber, t2.PartDesc 
from TEC.PARTSDATA as t1 
left outer join TEC.PARTSDATA as t2 on (t1.vender = t2.vender and t1.FwdPartNumber = t2.PartNumber) 
where t1.vender = 'TRWS'

Andy

Lenny77 · #3 (**permalink**) 11-24-09, 14:17

Code:

select t1.PartNumber, t1.PartDesc, t1.FwdTo, 
       t2.PartNumber, t2.PartDesc 
from 

(select * from TEC.PARTSDATA where vender = 'TRWS') t1 

Left Join 

(select * from TEC.PARTSDATA where vender = 'TRWS') t2

On t1.FwdPartNumber = t2.PartNumber

Lenny

marathonman · #4 (**permalink**) 11-24-09, 15:53

ARWinner:

Excellent! it works and the result set is returned within milliseconds. Thanks very much!
PS. got "Self Join" term from: Join (SQL) - Wikipedia, the free encyclopedia

Lenny77:

Thanks for your quick reply, from your response I have learned that there are several SQL approches to this problem.

Internet Services

Web Development

Digital Marketing

Consumer Tech