SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Item
(
Item_ID varchar(10),
Item_Name varchar(10),
Item_Type_ID varchar(10),
Item_Brand_ID varchar(10)
);
✓
CREATE TABLE Class
(
Class_Auto_ID varchar(10),
Class_Key varchar(10),
Class_ID varchar(10),
Class_Name varchar(10)
);
✓
INSERT INTO Item 
  (Item_ID, Item_Name, Item_Type_ID, Item_Brand_ID)
Values 
('2', '2', '', 'AA'),
('4', '4', '', ''),
('1', '1', 'A', ''),
('3', '3', 'B', 'AA')
✓
INSERT INTO Class 
(Class_Auto_ID, Class_Key, Class_ID, Class_Name)
  Values
('1', 'Item_Type', 'A', 'A'),
('3', 'Item_Brand', 'AA', 'AA'),
('2', 'Item_Type', 'B', 'B')
✓
select * from Item;


Item_ID
Item_Name
Item_Type_ID
Item_Brand_ID


2
2

AA

4
4



1
1
A


3
3
B
AA


select * from Class;


Class_Auto_ID
Class_Key
Class_ID
Class_Name


1
Item_Type
A
A

3
Item_Brand
AA
AA

2
Item_Type
B
B


Select a.Item_ID, a.Item_Name, a.Item_Type_ID, IFNULL(b.Class_Name, '') as Item_Type_Name
, a.Item_Brand_ID, IFNULL(c.Class_Name, '') as Item_Brand_Name
from Item a
Left Join Class b on b.Class_Key = 'Item_Type' and a.Item_Type_ID = b.Class_ID and a.Item_Name = b.Class_Auto_ID
Left Join Class c on c.Class_Key = 'Item_Brand' and a.Item_Brand_ID = c.Class_ID and a.Item_Name = c.Class_Auto_ID


Item_ID
Item_Name
Item_Type_ID
Item_Type_Name
Item_Brand_ID
Item_Brand_Name


2
2


AA


4
4





1
1
A
A



3
3
B

AA
AA