How to use one column's data to get data for another column in SQL SELECT statement

Question

I have hierarchical data stored in my table [AdjT] in SQL Server 2014. The total depth of data is 4 levels and is fixed.
i.e., [Parent - Child1 - Child2 - Child3]

The columns are: Adjt {Id, ParentId, Title, TypeM}
You can see the data in the image.

Multiple hierarchies (as per TypeM) are present in same table (you can see 2 parents)

What I require?

I need to fetch all the parent "Title" for any given Child.
e.g., below image shows the "hierarchy" for Id:4 for TypeM:AAA

What I have done so far

This query gives me the child row and its immediate parent.

select a.Title as P1, (select a1.Title from AdjT as a1 where a1.Id=a.ParentId) as P1 from Adjt as a where a.TypeM='AAA' and a.Id=4;

like this:

The issue:
But when I try to access P3 based on P2's ParentId, I get an error.

[The multi-part identifier "a1.ParentId" could not be bound.]

as per the below query:
select a.Title as P1 , (select a1.Title from AdjT as a1 where a1.Id=a.ParentId) as P2 , (select a2.Title from AdjT as a2 where a2.Id=a1.ParentId) as P3 --"a1.ParentId" ERROR from Adjt as a where a.TypeM='AAA' and a.Id=4;
Even if I write as P2.ParentId, it gives me:

The multi-part identifier "P2.ParentId" could not be bound.

What can I do to get this to work?

The workaround that I am using currently
I am using nested query to get to the data and hierarchy that I require.
Its done using the below query:
select a.Title as P1 , (select a1.Title from AdjT as a1 where a1.Id=a.ParentId) as P2 , (select a2.Title from AdjT as a2 where a2.Id=(select a1.ParentId from AdjT as a1 where a1.Id=a.ParentId)) as P3 , (select a3.Title from AdjT as a3 where a3.Id=(select a2.ParentId from AdjT as a2 where a2.Id=(select a1.ParentId from AdjT as a1 where a1.Id=a.ParentId))) as P4 from Adjt as a where a.TypeM='AAA' and a.Id=4;
Is there any better way to do this? Why can I only access the first column's parentId?
I cannot edit the schema of the table or how data is stored. The hierarchy must be fetched from bottom-up using leaf node's Id as shown above.
I tried to use CTE to get hierarchical data, but I want it as "columns" not "rows". Converting those CTE rows to columns in "code" is also not an option.
If any one can suggest a better way to do this, it would be most helpful.

Thank you for reading my question, any help would be appreciated.

My advice is that you look at Rahul's answer carefully and make sure that you fully understand how JOINs work. They are an invaluable part of SQL. The many subqueries that you use will result in very sloppy and hard to read (and maintain) code that will likely perform very badly. If you are going to be doing more SQL programming in the future, you should read some introductory books on the topic. — Tom H
– Tom H, Commented Jan 7, 2016 at 13:46

Rahul · Accepted Answer · 2016-01-07 13:36:23Z

2

You meant to do a self join like

select 
a.Title as P1
a1.Title as Tital1,
a2.Title as title2

from AdjT a 
join AdjT a1 on a1.Id=a.ParentId
join AdjT a2 on a2.Id=a1.ParentId
where a.TypeM = 'AAA'
and a.Id = 4;

answered Jan 7, 2016 at 13:36

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vaibhav J. Over a year ago

Thank you for simplifying it for me.

David Rushton · Accepted Answer · 2016-01-09 12:21:50Z

There are a couple of techniques you will need to achieve this.

Firstly self joins. In effect a self join allows you to convert rows into columns. By flattening your data in this style you can view a parent and child side by side.

Next we will need a OUTER JOIN. Because you want to search from any level there will not always be 3 parents (for example Id 2 only has one parent). A normal join will filter out a record if there is no parent present. Outer joins avoid this by returning NULL, when there is no matching record.

I've recreated your sample data inside a table VARIABLE. This makes it easier to share the example code.

Sample Data

/* Table VAR creates sample records we can 
 * all share.
 */
DECLARE @Sample TABLE
    (
        Id            INT PRIMARY KEY,
        ParentId    INT,
        Title        VARCHAR(50),
        TypeM        VARCHAR(3)
    )
;

/* Populate sample data.
 */
INSERT INTO @Sample
    (
        Id,
        ParentId,
        Title,
        TypeM
    )
VALUES
    (1, 0, 'Parent 1 - [P1]', 'AAA'),
    (2, 1, 'Child 1 - [C1] P1', 'AAA'),
    (3, 2, 'Child 2 - [C2] C1 P1', 'AAA'),
    (4, 3, 'Child 3 - [C3] C2 C1 P1', 'AAA'),
    (5, 0, 'Parent 1 - [P2]', 'DDD'),
    (6, 5, 'Child 1 - [C1] P2', 'DDD'),
    (7, 6, 'Child 2 - [C2] C1 P2', 'DDD'),
    (8, 7, 'Child 3 - [C3] C2 C1 P2', 'DDD')
;

Example

/* Self joins can extract the hierarchy.
 * Outer joins ensure results are always returned.
 */
SELECT
    s1.Title AS [P1],
    s2.Title AS [P2],
    s3.Title AS [P3],
    s4.Title AS [P4]
FROM
    @Sample AS s1
        LEFT OUTER JOIN @Sample AS s2        ON s2.Id = s1.ParentId
        LEFT OUTER JOIN @Sample AS s3        ON s3.Id = s2.ParentId
        LEFT OUTER JOIN @Sample AS s4        ON s4.Id = s3.ParentId
WHERE
    s1.Id = 4
;

Try replacing WHERE id = 4 with 3 to see the outer join in action.

Thanks. This solved my issue. The NULL value returned as a result of using OUTER JOIN was useful.

Collectives™ on Stack Overflow

How to use one column's data to get data for another column in SQL SELECT statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related