Добрый день, помогите разобраться
Необходимо написать древовидный запрос MS SQL возвращающий дерево спецификации (состав) шины (таблица MD_BOM),
где: MAT_SAP_CODE – код материала
CHILD_MAT_SAP_CODE – код дочернего материала, из которого изготовлен MAT_SAP_CODE


CREATE TABLE [dbo].[MD_BOM](
[MAT_SAP_CODE] [varchar](9) NOT NULL,
[MAT_VARIANT] [varchar](10) NOT NULL,
[CNT_CODE] [varchar](3) NOT NULL,
[LMM_SEQUENCE] [int] NOT NULL,
[MT_CODE] [varchar](8) NOT NULL,
[BV_STATUS] [varchar](5) NOT NULL,
[CHILD_MAT_VARIANT] [varchar](10) NULL,
[CHILD_MAT_SAP_CODE] [varchar](9) NULL,
[CHILD_CNT_CODE] [varchar](3) NULL,
CONSTRAINT [XPKMD_BOM] PRIMARY KEY CLUSTERED
(
[MAT_SAP_CODE] ASC,
[MAT_VARIANT] ASC,
[CNT_CODE] ASC,
[MT_CODE] ASC,
[BV_STATUS] ASC,
[LMM_SEQUENCE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[MD_BOM] WITH CHECK ADD CONSTRAINT [R_199] FOREIGN KEY([MAT_SAP_CODE], [MAT_VARIANT], [CNT_CODE])
REFERENCES [dbo].[MD_MATERIALS] ([MAT_SAP_CODE], [MAT_VARIANT], [CNT_CODE])


ALTER TABLE [dbo].[MD_BOM] WITH CHECK ADD CONSTRAINT [R_199_CHILD] FOREIGN KEY([CHILD_MAT_SAP_CODE], [CHILD_MAT_VARIANT], [CHILD_CNT_CODE])
REFERENCES [dbo].[MD_MATERIALS] ([MAT_SAP_CODE], [MAT_VARIANT], [CNT_CODE])
CREATE TABLE [dbo].[MD_MATERIALS](
[MAT_CODE] [varchar](6) NOT NULL,
[CNT_CODE] [varchar](3) NOT NULL,
[MAT_SAP_CODE] [varchar](9) NOT NULL,
[MAT_PRODUCTION_GROUP] [varchar](10) NULL,
[MAT_DESC] [varchar](40) NULL,
CONSTRAINT [XPKMD_MATERIALS] PRIMARY KEY CLUSTERED
(
[MAT_SAP_CODE] ASC,
[MAT_VARIANT] ASC,
[CNT_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


До этого никогда не сталкивалась с такими задачами

Модератор: https://www.sql.ru/forum/1314459/pomogite-razobratsya-c-ierarhiey