Results 1 to 4 of 4

Thread: Self Join

  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Self Join

    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,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Usually I use join subselect

    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

  4. #4
    Join Date
    Nov 2009
    Posts
    2

    Re: Self Join

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •