List parent with childs

  • 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

  • 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.

  • 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