Select Parent and Child - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select Parent and Child

Author

Topic

udaaf
Starting Member

22 Posts

Posted - 2014-09-04 : 05:33:17

How to select parent and child together in table with HierarchyID.


CREATE TABLE #BOMTbl
(
  ItemNo   HIERARCHYID NOT NULL,
  Lvl AS ItemNo.GetLevel() PERSISTED,
  MatID VARCHAR(25) NULL,
  CONSTRAINT PK_#BOMTbl PRIMARY KEY NONCLUSTERED(ItemNo)
);

insert into #BOMTbl (ItemNo,MatID) values
('/1/',''),
('/1/1/',''),
('/1/1/1/','MA-A'),
('/1/1/2/','MA-A'),
('/1/1/3/','MA-B'),
('/1/2/',''),
('/1/2/1/','MA-B'),
('/1/2/2/','MA-B'),
('/1/3/',''),
('/1/3/1/','PW-A'),
('/1/3/2/','PW-A'),
('/1/4/',''),
('/1/4/1/','PW-B'),
('/1/4/2/','PW-B'),
('/1/5/','0001'),
('/1/6/','0001'),
('/1/7/','0002'),
('/1/8/','0003')

I've try some code but I don't have knowledge how to join two table where the result is like this one :


ItemNo	ItemID	Lvl	MatID
0x5AC0	/1/1/	2	
0x5AD6	/1/1/1/	3	MA-A
0x5ADA	/1/1/2/	3	MA-A
0x5B40	/1/2/	2	
0x5ADE	/1/1/3/	3	MA-B
0x5B56	/1/2/1/	3	MA-B
0x5B5A	/1/2/2/	3	MA-B

Here's my query :


select itemno, itemno.ToString() as ItemID, Lvl, MatID 
from #BOMTbl 
where MatID LIKE 'MA%'

Select ItemNo , ItemNo.ToString() as ItemID,Lvl, MatID 
from #BOMTbl
where ItemNo  in (
           Select ItemNo .GetAncestor(ItemNo .GetLevel()-(select max (lvl-1) from #BOMTbl where MatID LIKE 'MA%')).ToString()
           from #BOMTbl 
           where MatID LIKE 'MA%'
           )

Please tell me how to get the table like above.

Regards,

UdaAf

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-09-04 : 07:46:29

post the table structure of the other table you want to join

Javeed Ahmed

udaaf
Starting Member

22 Posts

Posted - 2014-09-04 : 20:39:09

Dear Ahmad,

I just have 1 table. I wanna select parent and child base on MatID. I wanna the result like this one :


ItemNo	ItemID	Lvl	MatID
0x5AC0	/1/1/	2	
0x5AD6	/1/1/1/	3	MA-A
0x5ADA	/1/1/2/	3	MA-A
0x5B40	/1/2/	2	
0x5ADE	/1/1/3/	3	MA-B
0x5B56	/1/2/1/	3	MA-B
0x5B5A	/1/2/2/	3	MA-B

Here's the master table.


CREATE TABLE #BOMTbl
(
  ItemNo   HIERARCHYID NOT NULL,
  Lvl AS ItemNo.GetLevel() PERSISTED,
  MatID VARCHAR(25) NULL,
  CONSTRAINT PK_#BOMTbl PRIMARY KEY NONCLUSTERED(ItemNo)
);

insert into #BOMTbl (ItemNo,MatID) values
('/1/',''),
('/1/1/',''),
('/1/1/1/','MA-A'),
('/1/1/2/','MA-A'),
('/1/1/3/','MA-B'),
('/1/2/',''),
('/1/2/1/','MA-B'),
('/1/2/2/','MA-B'),
('/1/3/',''),
('/1/3/1/','PW-A'),
('/1/3/2/','PW-A'),
('/1/4/',''),
('/1/4/1/','PW-B'),
('/1/4/2/','PW-B'),
('/1/5/','0001'),
('/1/6/','0001'),
('/1/7/','0002'),
('/1/8/','0003')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources