Query to Retrive Master and Child records in the same row

ramadadi
ramadadi used Ask the Experts™
on
I have a table T_CodeValue and T_Code Linke

1. T_CodeValue table
============================
CodeValueID,   I   Code Desc          I  
============================
1           I     Category          I  
2           I     SubCategory    |
--------------------------------------------

2. T_Code Linke
=====================================
LInkID   |   CodeValueID  |  LinkedCodeValueID |
=====================================
1          |        1                |                2                |
--------------------------------------------------------
based on the above two tables i need to the both records in one row
as  below Result set
================================
  Code Value      |     Linked Code value  |
================================
 Category           |          SubCategor
-------------------------------------------------
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
SELECT a.CodeDesc as CodeValue, b.CodeDesc as LinkCodeValue
FROM T_Code L
INNER JOIN  T_CodeValue  a ON l.CodeValueID = a.CodeValueID
LEFT JOIN T_CodeValue  b ON l.LinkCodeValueID = b.CodeValueID
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
question: how many levels will there be to be diplayed on 1 rows?
just 2 levels each time?

if yes:
select p.code_desc, c.code_desc linked_code_value
  from t_codevalue p
  left join t_code_link l
    on l.codevalueid = p.codevalueid
  left join t_codevalue c
    on c.codevalueid = l.linkedcodevalueid

Open in new window

Author

Commented:
As of now only 2 levels, how? if i have multiple levels?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>if i have multiple levels?
you have to clarify then what the output should be...
if you want 1 level more to be displayed, add 2 more left joins as shown.
however, it mainly depends on what you want/need to do with the data returned by the query-

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial