0

I have a table like this

enter image description here

Please forgive the column names they are driven by internal standards. What I would like to know is how to query this self-referencing table so the output is something like this:

| PARENT REASON |    CHILD REASON  |
| ---------------------------------|
| Electrical    | Wire Broke       |
| Electrical    | Fuse Blown       |
| Mechanical    | Bad Gear         |
| Mechanical    | Bolt Broke       |

You help is greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nick Birke
  • 137
  • 2
  • 12
  • 1
    Any chance that you'll need to handle a deeper search, e.g. Electrical > Fuse Blown > Insulation Failure? A Common Table Expression (CTE) can handle recursion. – HABO Sep 17 '13 at 19:34

2 Answers2

1
SELECT T2.strReason as [PARENT REASON], T1.strReason as [CHILD REASON]
FROM IngTable as T1
JOIN IngTable as T2
  ON T1.IngReasonParent = T2.IngReason
ngneema
  • 444
  • 3
  • 7
  • var query = from T1 in IngTable join T2 in IngTable on T1.IngReasonParent = T2.IngReasonParent Select T2.strReason, T1.strReason should work – ngneema Sep 17 '13 at 18:36
0

I believe you should break that apart into a category table, but...

SELECT SELECT a.strReason as [PARENT REASON], b.strReason as [CHILD REASON]
FROM table1 AS a 
INNER JOIN table1 as b ON b.lngReasonParent = a.lngReason
Elias
  • 2,602
  • 5
  • 28
  • 57