October 25, 2009 at 8:57 pm
Can someone help me writing a storeproc that will list all parents with is child (sub-parts), level by level.
Description of the table
CREATE TABLE [dbo].[BM010115](
[PPN_I] [char](31) NOT NULL,
[CPN_I] [char](31) NOT NULL,
[BOMCAT_I] [smallint] NOT NULL,
[BOMNAME_I] [char](15) NOT NULL,
[SUBCAT_I] [smallint] NOT NULL,
CONSTRAINT [PKBM010115] PRIMARY KEY CLUSTERED
(
[PPN_I] ASC,
[BOMCAT_I] ASC,
[BOMNAME_I] ASC,
[CPN_I] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
For example we can locate a parent with this select statement
Level 1
select PPN_I,CPN_I,BOMCAT_I,BOMNAME_I,SUBCAT_I from dbo.bm010115 where ppn_i like 'POSTE9F-07 REV A00%'
PPN_I CPN_I BOMCAT_I BOMNAME_I SUBCAT_I
------------------------------- ------------------------------- -------- --------------- --------
POSTE9F-07 REV A00 ASNA2531-3 1 0
POSTE9F-07 REV A00 MMBTF53120011000-1 REV NC 1 0
POSTE9F-07 REV A00 NAS1149F0332P 1 0
POSTE9F-07 REV A00 NAS1801-3-14 1 0
POSTE9F-07 REV A00 NSA9117-62 1 0
POSTE9F-07 REV A00 POSTE8F-06 REV A00 1 1
POSTE9F-07 REV A00 PQ10050-110-02 1 0
POSTE9F-07 REV A00 PQ10054-022-04 1 0
Then for level 2 :
if this query return true :
if (select count(*) from dbo.bm010115 where ppn_i like 'POSTE9F-07 REV A00%' and subcat_i=1 )>0
Thats mean we have a child and we need to verify if that child have some child
select PPN_I,CPN_I,BOMCAT_I,BOMNAME_I,SUBCAT_I from dbo.bm010115 where ppn_i like 'POSTE9F-07 REV A00%' and subcat_i=1
PPN_I CPN_I BOMCAT_I BOMNAME_I SUBCAT_I
------------------------------- ------------------------------- -------- --------------- --------
POSTE9F-07 REV A00 POSTE8F-06 REV A00 1 1
(1 row(s) affected)
Then for level 3 :
select PPN_I,CPN_I,BOMCAT_I,BOMNAME_I,SUBCAT_I from dbo.bm010115 where ppn_i like 'POSTE8F-06 REV A00%'
PPN_I CPN_I BOMCAT_I BOMNAME_I SUBCAT_I
------------------------------- ------------------------------- -------- --------------- --------
POSTE8F-06 REV A00 F531 12325-200-00 1 0
POSTE8F-06 REV A00 F531 20220-200-00 1 0
POSTE8F-06 REV A00 F531 20220-202-00 1 0
POSTE8F-06 REV A00 F531 20220-204-00 1 0
POSTE8F-06 REV A00 F531 20220-206-00 1 0
POSTE8F-06 REV A00 F531 20220-208-00 1 0
POSTE8F-06 REV A00 F531 20404-200-00 1 0
POSTE8F-06 REV A00 F531 20408-200-00 1 0
POSTE8F-06 REV A00 F531 20411-200-00 REV D00 1 1
POSTE8F-06 REV A00 F531 20412-200-00 REV B00 1 0
POSTE8F-06 REV A00 F531 20413-200-00 1 0
POSTE8F-06 REV A00 F531 20414-200-00 1 0
POSTE8F-06 REV A00 F531 20417-200-00 1 0
POSTE8F-06 REV A00 F531 20421-200-00 1 0
POSTE8F-06 REV A00 F531 20422-200-00 REV A00 1 4
POSTE8F-06 REV A00 F531 20432-200-00 REV B00 1 0
POSTE8F-06 REV A00 F539 25070-000-00 REV B00 1 1
POSTE8F-06 REV A00 F539 25078-000-00 REV C00 1 1
POSTE8F-06 REV A00 F539 25115-200-00 1 0
POSTE8F-06 REV A00 F539 25116-000-00 1 0
POSTE8F-06 REV A00 F539 25117-000-00 REV B00 1 1
POSTE8F-06 REV A00 F539 25132-000-02 REV A00 1 0
POSTE8F-06 REV A00 F539 25132-001-02 REV A00 1 0
POSTE8F-06 REV A00 F539 25136-000-03 REV B00 1 0
POSTE8F-06 REV A00 F539 25136-001-03 REV B00 1 0
POSTE8F-06 REV A00 F539 25137-200-00 1 0
POSTE8F-06 REV A00 F539 25138-200-00 1 0
POSTE8F-06 REV A00 F539 25139-200-00 1 0
POSTE8F-06 REV A00 F539 25140-200-00 1 0
POSTE8F-06 REV A00 F539 25141-200-00 1 0
POSTE8F-06 REV A00 F539 25141-201-00 1 0
POSTE8F-06 REV A00 F539 25142-000-01 REV A00 1 0
POSTE8F-06 REV A00 F539 25142-001-01 REV A00 1 0
POSTE8F-06 REV A00 F539 25143-000-00 REV D00 1 0
POSTE8F-06 REV A00 F539 25143-001-00 REV D00 1 0
POSTE8F-06 REV A00 F539 25169-000-00 1 0
POSTE8F-06 REV A00 F539 25170-000-00 1 0
POSTE8F-06 REV A00 F539 25233-200-00 REV A00 1 0
POSTE8F-06 REV A00 F539 25233-201-00 REV A00 1 0
POSTE8F-06 REV A00 F539 25240-200-00 REV A00 1 0
POSTE8F-06 REV A00 F539 25472-000-00 REV A00 1 1
POSTE8F-06 REV A00 F539 25481-000-00 1 0
POSTE8F-06 REV A00 F539 25481-001-00 1 0
POSTE8F-06 REV A00 F539 25484-000-00 1 0
POSTE8F-06 REV A00 F539 25484-001-00 1 0
POSTE8F-06 REV A00 F539 25556-000-00 1 0
POSTE8F-06 REV A00 HARDWARE POSTE8F 1 1
POSTE8F-06 REV A00 KIT PINTLE PIN REV A00 1 1
POSTE8F-06 REV A00 MS21061-3-AIF 1 0
POSTE8F-06 REV A00 NAS1766-3-AIF 1 0
POSTE8F-06 REV A00 POSTE8-06 REV A00 1 1
POSTE8F-06 REV A00 PQ10006-018-00 1 0
POSTE8F-06 REV A00 PQ10010-020-06 1 0
POSTE8F-06 REV A00 PQ10010-021-04 1 0
POSTE8F-06 REV A00 PQ10010-141-00 1 0
POSTE8F-06 REV A00 PQ10050-055-03 1 0
POSTE8F-06 REV A00 PQ10053-091-03 1 0
(57 row(s) affected)
Then for level 4:
if this query return true :
if (select count(*) from dbo.bm010115 where ppn_i like 'POSTE8F-06 REV A00%' and subcat_i=1 )>0
select PPN_I,CPN_I,BOMCAT_I,BOMNAME_I,SUBCAT_I from dbo.bm010115 where ppn_i like 'F539 25117-000-00 REV B00%'
PPN_I CPN_I BOMCAT_I BOMNAME_I SUBCAT_I
------------------------------- ------------------------------- -------- --------------- --------
F539 25117-000-00 REV B00 ASNA2050DXJ4009 1 0
F539 25117-000-00 REV B00 F539 25118-000-00 REV A00 1 1
F539 25117-000-00 REV B00 F539 25119-200-00 1 0
F539 25117-000-00 REV B00 PQ10010-020-06 1 0
(4 row(s) affected)
and so on. As long As we have child with subcat_i=1 we go to list it and increment the level.
**Note a child can also be a parent, it becomes a parent when the subcat_i=1
Thanks in advance
Stan
October 25, 2009 at 9:41 pm
You might want to read this: http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
This is hard to do in 2000. Easier in 2005 with CTEs and recursion.
October 27, 2009 at 10:35 am
Thanks for the quick answer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply