Hello, All
My scenario is around packing process, so a bit of a background:
1. We pack our products into a box.
2. Boxes go into one case.
3. Cases go into one master case.
4. Master cases go onto a pallet.
5. Several pallets are logically grouped into one consignment and shipped, once ready.
I want to present the hierarchy of a given consignment, including parent Id, level Id and the level within the hierarchy.
To do so I use CTE implementation as below, but I get duplicate records, e.g. parent Id 'M2' and level Id 'C1' on tree level 2 shown twice. I can use DISTINCT, but I am sure it is not the right way and I miss something.
Any help will be appreciated, as always!
DROP TABLE IF EXISTS consignment_levels;
GO
CREATE TABLE consignment_levels(
consignment_id INT NOT NULL,
parent_id CHAR(10) NULL,
level_id CHAR(10) NOT NULL,
level_type CHAR(10) NOT NULL,--case, master_case, pallet
);
CREATE UNIQUE INDEX consignment_levels_U01 ON consignment_levels (consignment_id,parent_id,level_id);
GO
DECLARE @v_consignment_id INT = 1000;
DECLARE @c_pallet CHAR(6) = 'PALLET',
@c_master_case CHAR(6) = 'MASTER',
@c_case CHAR(4) = 'CASE',
@c_box CHAR(3) = 'BOX';
INSERT INTO consignment_levels(consignment_id,parent_id,level_id, level_type)
VALUES (@v_consignment_id,NULL,'P1',@c_pallet),
(@v_consignment_id,NULL,'P2',@c_pallet),
(@v_consignment_id,NULL,'P3',@c_pallet),
(@v_consignment_id,NULL,'P4',@c_pallet),
(@v_consignment_id,NULL,'P5',@c_pallet),
(@v_consignment_id,NULL,'P6',@c_pallet),
(@v_consignment_id,'P2','M1',@c_master_case),
(@v_consignment_id,'P2','M2',@c_master_case),
(@v_consignment_id,'P2','M3',@c_master_case),
(@v_consignment_id,'P2','M4',@c_master_case),
(@v_consignment_id,'P3','M1',@c_master_case),
(@v_consignment_id,'P3','M2',@c_master_case),
(@v_consignment_id,'P3','M3',@c_master_case),
(@v_consignment_id,'P3','M4',@c_master_case),
(@v_consignment_id,'M3','C1',@c_case),
(@v_consignment_id,'M3','C2',@c_case),
(@v_consignment_id,'M3','C3',@c_case),
(@v_consignment_id,'M3','C4',@c_case),
(@v_consignment_id,'M3','C5',@c_case),
(@v_consignment_id,'M3','C6',@c_case),
(@v_consignment_id,'M3','C7',@c_case),
(@v_consignment_id,'M3','C8',@c_case),
(@v_consignment_id,'M3','C9',@c_case),
(@v_consignment_id,'M2','C1',@c_case),
(@v_consignment_id,'M4','C1',@c_case),
(@v_consignment_id,'M4','C2',@c_case),
(@v_consignment_id,'C1','B1',@c_box),
(@v_consignment_id,'C1','B2',@c_box),
(@v_consignment_id,'C1','B3',@c_box),
(@v_consignment_id,'C1','B4',@c_box),
(2,NULL,'P2',@c_pallet),
(2,NULL,'P3',@c_pallet),
(2,NULL,'P4',@c_pallet);
WITH consignment_tree (consignment_id,parent_id,level_id, level_type,tree_level)
AS
(
SELECT cc.consignment_id,
cc.parent_id,
cc.level_id,
cc.level_type,
0
FROM consignment_levels cc
WHERE cc.parent_id IS NULL
UNION ALL
SELECT cc.consignment_id,
cc.parent_id,
cc.level_id,
cc.level_type,
tree_level+1
FROM consignment_levels cc
INNER JOIN consignment_tree ct
ON ct.level_id = cc.parent_id
AND ct.consignment_id = cc.consignment_id
)
SELECT * FROM consignment_tree ct
WHERE ct.consignment_id = @v_consignment_id
ORDER BY ct.tree_level,
ct.parent_id,
ct.level_id
OPTION (MAXRECURSION 5);
You're getting the duplicates because M2 has two parents -- P2 & P3.
If you don't care about that and need the single row for each such case you'll need to use GROUP BY or DISTINCT.
Or CROSS APPLY ct instead of INNER JOIN, using SELECT TOP 1 to arbitrarily join to only one parent.
June 10, 2022 at 5:56 am
June 10, 2022 at 7:30 am
it kink of looks your data is not a real representation of what happens.
you can't have the same case (C1) going to 2 distinct master cases (m3 and M4) and you can't have the same master case (M1) going to 2 distinct pallets (M1 going to both P1 and P2)
once you sort out your data then the query is likely to give what you need.
June 10, 2022 at 8:03 am
You're getting the duplicates because M2 has two parents -- P2 & P3. If you don't care about that and need the single row for each such case you'll need to use GROUP BY or DISTINCT.
Or CROSS APPLY ct instead of INNER JOIN, using SELECT TOP 1 to arbitrarily join to only one parent.
Sooooooo embarrassing... I didn't pay attention I re-used same parent IDs... Apologies to all and thank you as usual.
June 21, 2022 at 11:11 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply