2

I'm making a "bom" list entry into the "materials" table.

I bring the materials defined for the product group to the grid so that users do not forget the relevant material.

Users define through this template.

But they add extra material (other than the group template) if they want.

If there is a record in the second select statement; I want to not show the record in 1 select statement.

I understand from the lvl column that this record should be crushed in the second select clause. (Definition has been made for template numbered 2)

enter image description here

CREATE TABLE [dbo].[materials](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [productCode] [varchar](50) NULL,
    [description] [varchar](250) NULL,
    [usageQauntity] [float] NULL,
    [process] [varchar](50) NULL,
    [vendor] [varchar](50) NULL,
    [lvl] [int] NULL,
 CONSTRAINT [PK_materials] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[product](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [code] [varchar](50) NULL,
    [name] [varchar](250) NULL,
    [groupCode] [varchar](50) NULL,
    [unit] [varchar](10) NULL,
    [price] [float] NULL,
    [minStock] [int] NULL,
    [maxStock] [int] NULL,
 CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[materials] ON 
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (1, N'pencil', N'paint chemical', 0.15, N'1', N'ABC', 1)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (2, N'pencil', N'pen inside', 1, N'1', N'DEF', 2)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (4, N'pencil', N'cover', 1, N'2', N'GHD', 3)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (5, N'pencil', N'brand label', 1, N'0', N'ABC', 4)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (6, N'lp', N'top eraser', 1, N'0', N'', 0)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (7, N'lp', N'DEF brand pencil inside', 1, N'1', N'DEF', 2)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (8, N'eraser', N'chemical', 1, N'0', N'ABC', 4)
INSERT [dbo].[materials] ([id], [productCode], [description], [usageQauntity], [process], [vendor], [lvl]) VALUES (9, N'eraser', N'brand label', 1, N'0', N'ABC', 4)
SET IDENTITY_INSERT [dbo].[materials] OFF

SET IDENTITY_INSERT [dbo].[product] ON 
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (1, N'lp', N'lead pencil', N'pencil', N'piece', 15, 1000, 2000)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (2, N'bp', N'ball-point pen', N'pencil', N'piece', 25, 2000, 3000)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (3, N'sp', N'stylographic pen', N'pencil', N'piece', 50, 1000, 2000)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (4, N'me', N'Magnetic eraser', N'eraser', N'piece', 30, 1000, 1500)
INSERT [dbo].[product] ([id], [code], [name], [groupCode], [unit], [price], [minStock], [maxStock]) VALUES (5, N'se', N'Soft Eraser', N'eraser', N'piece', 20, 1000, 2000)
SET IDENTITY_INSERT [dbo].[product] OFF


13 rows affected
select * from product
select * from materials
id code name groupCode unit price minStock maxStock
1 lp lead pencil pencil piece 15 1000 2000
2 bp ball-point pen pencil piece 25 2000 3000
3 sp stylographic pen pencil piece 50 1000 2000
4 me Magnetic eraser eraser piece 30 1000 1500
5 se Soft Eraser eraser piece 20 1000 2000
id productCode description usageQauntity process vendor lvl
1 pencil paint chemical 0.15 1 ABC 1
2 pencil pen inside 1 1 DEF 2
4 pencil cover 1 2 GHD 3
5 pencil brand label 1 0 ABC 4
6 lp top eraser 1 0 0
7 lp DEF brand pencil inside 1 1 DEF 2
8 eraser chemical 1 0 ABC 4
9 eraser brand label 1 0 ABC 4
select p.id,p.code,m2.*
from product p
left join materials m2 on m2.productCode=p.groupCode
where p.code='lp'

select  p.id,p.code,m1.*
from product p
left join materials m1 on m1.productCode=p.code
where p.code='lp'
id code id productCode description usageQauntity process vendor lvl
1 lp 1 pencil paint chemical 0.15 1 ABC 1
1 lp 2 pencil pen inside 1 1 DEF 2
1 lp 4 pencil cover 1 2 GHD 3
1 lp 5 pencil brand label 1 0 ABC 4
id code id productCode description usageQauntity process vendor lvl
1 lp 6 lp top eraser 1 0 0
1 lp 7 lp DEF brand pencil inside 1 1 DEF 2
--test: Since lvl=2 is defined in the product, materials.id=2 should not appear.
select  p.id,p.code,m1.*
from product p
left join materials m1 on (m1.productCode=p.code or m1.productCode=p.groupCode)
where p.code='lp'
id code id productCode description usageQauntity process vendor lvl
1 lp 1 pencil paint chemical 0.15 1 ABC 1
1 lp 2 pencil pen inside 1 1 DEF 2
1 lp 4 pencil cover 1 2 GHD 3
1 lp 5 pencil brand label 1 0 ABC 4
1 lp 6 lp top eraser 1 0 0
1 lp 7 lp DEF brand pencil inside 1 1 DEF 2

fiddle

1
  • Why in the world can you have a ProductCode that is also a GroupCode? That's a pretty large deficiency in the data model (not to mention a lack of keys) which is avoided with the BOM model. A (short) primer here: stackoverflow.com/a/17652385/13942986
    – user212533
    Oct 26, 2022 at 13:51

1 Answer 1

3
WITH
  cte1 AS (
select p.id p_id,p.code p_code,m2.*
from product p
left join materials m2 on m2.productCode=p.groupCode
where p.code='lp'
),
  cte2 AS (
select p.id p_id,p.code p_code,m1.*
from product p
left join materials m1 on m1.productCode=p.code
where p.code='lp'
)
SELECT cte1.*
FROM cte1
LEFT JOIN cte2 ON cte1.lvl = cte2.lvl
WHERE cte2.lvl IS NULL
UNION ALL
SELECT *
FROM cte2;

https://dbfiddle.uk/KqDsDnko

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.