Tough Query Help

[JMay0816]

Need some help retrieving a additional field for a query.

Here's my sample data:

DB1:
PartNo: PDescription: Status: Master:
100 widget assembly #1 Active
101 widget assembly #2 Active
102 widget assmebly #3 Active
103 drive line Active 101
104 drive line Active 102


Output should look like the following:
PartNo Description Status MasterDesc
100 widget assembly #1 Active widget assembly #1
101 widget assembly #2 Active widget assembly #2
102 widget assmebly #3 Active widget assembly #3
103 drive line Active widget assembly #1
104 drive line Active widget assembly #2



I can generate the list with the exception of the MasterDescription field for the output. I can't quite create the query to link that info.

Can someone lend a hand?

Thx

 

[r937]

select p.PartNo
     , p.PDescription as Description
     , p.Status
     , coalesce(p.Master,m.PDescription) as Master
  from DB1 as p
left outer
  join DB1 as m
    on m.PartNo = p.Master

r937.com | rudy.ca