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 |