Query to select parent details from child table

Question

Post reply

Query to select parent details from child table

sqlquery29

Ten Centuries

Points: 1319
More actions
March 3, 2015 at 7:09 am

#298239

Hi Team,
I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Please anyone suggest me with the select query where i need to select the Code for all Parent_Id's.
Thanks in Advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ronnie Rahman Say Hey Kid Points: 674 More actions · Answer 1

Do you have a separate Parent table?

Otherwise, a simple query below will return all Parent_ID in Equipment table.

SELECT

Parent_Id

FROM Equipment

GROUP BY Parent_Id

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlquery29 Ten Centuries Points: 1319 More actions · Answer 3

We do not have separate Parent table.

How do i join the child table with itself to get the Parent_ID codes?

Thanks,

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

sqlquery29 (3/25/2015)
We do not have separate Parent table.
How do i join the child table with itself to get the Parent_ID codes?
Thanks,

You would join on YourChildID = YourParentID.

If you want more detailed assistance then please read my last post here. Read the article I mentioned and heed the advice found within.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlquery29 Ten Centuries Points: 1319 More actions · Answer 5

Hi ...I got sample DDL's

CREATE TABLE Equipment(

[Equipment_ID] [bigint] NOT NULL,

[Version] [int] NOT NULL,

[Code] [nvarchar](50) NOT NULL,

[Description] [nvarchar](100) NOT NULL,

[IsActive] [bit] NOT NULL,

[TreeLevel] [smallint] NOT NULL,

[Parent_Id] [int] NULL,

CONSTRAINT [pk_Equipment] PRIMARY KEY CLUSTERED

(

[Equipment_ID] ASC

)

INSERT INTO Equipment (Equipment_ID,Version,Code,Description,IsActive,TreeLevel,Parent_Id)

SELECT '1009','2','ZZZ','Dis','1','1','1' UNION ALL

SELECT '1010','3','YYY_1','house','1','2','1017' UNION ALL

SELECT '1011','2','YYY_11','To Be Repaired','1','3','1010' UNION ALL

SELECT '1012','2','YYY_12','Repaired','1','3','1010' UNION ALL

SELECT '1013','3','YYY_2','Vendor','1','2','1017' UNION ALL

SELECT '1014','1','YYY_21','Vendor 1','1','3','1013' UNION ALL

SELECT '1015','1','YYY_22','Vendor x','1','3','1013' UNION ALL

SELECT '1016','3','YYY_3','Workshop','1','2','1017' UNION ALL

SELECT '1017','1','YYY','Rotable Items','1','1','1' UNION ALL

SELECT '1018','9','2500','Farm','1','1','1' UNION ALL

SELECT '1019','5','2600','HHH','1','1','1' UNION ALL

SELECT '1020','4','29000','JJJ','1','1','1' UNION ALL

SELECT '1021','9','265000','KKK','1','1','1' UNION ALL

SELECT '1022','10','25146','Finished Product','1','2','11172' UNION ALL

SELECT '1023','5','2300','OOO','1','1','1' UNION ALL

SELECT '1024','4','2700','Utilities','1','1','1' UNION ALL

SELECT '3153','12','2501','LOC 1','1','2','1018' UNION ALL

SELECT '3154','9','2502','LOC 2','1','2','1018'

now as we see, the parent_id is nothing but the Equipment_id in the table. I want to select the COde for all parent_Id's in my query.

Appreciate help.

Thanks,

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

sqlquery29 (4/29/2015)
Hi ...I got sample DDL's
CREATE TABLE Equipment(
[Equipment_ID] [bigint] NOT NULL,
[Version] [int] NOT NULL,
[Code] [nvarchar](50) NOT NULL,
[Description] [nvarchar](100) NOT NULL,
[IsActive] [bit] NOT NULL,
[TreeLevel] [smallint] NOT NULL,
[Parent_Id] [int] NULL,
CONSTRAINT [pk_Equipment] PRIMARY KEY CLUSTERED
(
[Equipment_ID] ASC
)
)
INSERT INTO Equipment (Equipment_ID,Version,Code,Description,IsActive,TreeLevel,Parent_Id)
SELECT '1009','2','ZZZ','Dis','1','1','1' UNION ALL
SELECT '1010','3','YYY_1','house','1','2','1017' UNION ALL
SELECT '1011','2','YYY_11','To Be Repaired','1','3','1010' UNION ALL
SELECT '1012','2','YYY_12','Repaired','1','3','1010' UNION ALL
SELECT '1013','3','YYY_2','Vendor','1','2','1017' UNION ALL
SELECT '1014','1','YYY_21','Vendor 1','1','3','1013' UNION ALL
SELECT '1015','1','YYY_22','Vendor x','1','3','1013' UNION ALL
SELECT '1016','3','YYY_3','Workshop','1','2','1017' UNION ALL
SELECT '1017','1','YYY','Rotable Items','1','1','1' UNION ALL
SELECT '1018','9','2500','Farm','1','1','1' UNION ALL
SELECT '1019','5','2600','HHH','1','1','1' UNION ALL
SELECT '1020','4','29000','JJJ','1','1','1' UNION ALL
SELECT '1021','9','265000','KKK','1','1','1' UNION ALL
SELECT '1022','10','25146','Finished Product','1','2','11172' UNION ALL
SELECT '1023','5','2300','OOO','1','1','1' UNION ALL
SELECT '1024','4','2700','Utilities','1','1','1' UNION ALL
SELECT '3153','12','2501','LOC 1','1','2','1018' UNION ALL
SELECT '3154','9','2502','LOC 2','1','2','1018'
now as we see, the parent_id is nothing but the Equipment_id in the table. I want to select the COde for all parent_Id's in my query.
Appreciate help.
Thanks,

This is part of the information required. What do you want as output? Just saying you want to select the code for parent_Id's will have me write you a query like this.

select code

from Equipment

where Parent_Id is not null

I doubt that is what you are really looking for. Either an explanation of what you actually want or even better the actual values you want returned will help me figure out what you are looking for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlquery29 Ten Centuries Points: 1319 More actions · Answer 7

Hi Sean,

DDL for my target looks like:

CREATE TABLE Equipment_TGT(

[Equipment_ID] [bigint] NOT NULL,

[Code] [nvarchar](50) NOT NULL,

[Description] [nvarchar](100) NOT NULL,

[TreeLevel] [smallint] NOT NULL,

[Parent_Id] [int] NULL,

[Parent_Code] [nvarchar](50) NOT NULL,

CONSTRAINT [pk_Equipment] PRIMARY KEY CLUSTERED

(

[Equipment_ID] ASC

)

and the data should look like :

INSERT INTO Equipment (Equipment_ID,Code,Description,TreeLevel,Parent_Id,Parent_Code)

SELECT '1010','YYY_1','house','2','1017','YYY' UNION ALL

SELECT '1011','YYY_11','To Be Repaired','3','1010','YYY_1' UNION ALL

SELECT '1012','YYY_12','Repaired','3','1010','YYY_1' UNION ALL

SELECT '1013','YYY_2','Vendor','2','1017','YYY' UNION ALL

SELECT '1014',,'YYY_21','Vendor 1','3','1013','YYY_2' UNION ALL

SELECT '1015','YYY_22','Vendor x','3','1013','YYY_2' UNION ALL

SELECT '1016','YYY_3','Workshop','2','1017','YYY' UNION ALL

SELECT '1017','YYY','Rotable Items','1','1','ROOT' UNION ALL

SELECT '1018','2500','Farm','1','1','ROOT' UNION ALL

As in the above target table, for the Equipment_Id = '1010' the code is 'YYY_1' and its parent_ID is '1017' and parent_code is 'YYY'. I need display the results as in the above target table.

Hope this helps, please let me know.

Thanks

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 8

Looks like you need just self join the table

select a.*, b.Code as parentCode

from Equipment a

join Equipment b on b.Equipment_ID = a.Parent_Id