By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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)
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 |