By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1 (
ID INT IDENTITY(1,1) PRIMARY KEY,
OCStypeID INT NOT NULL,
OCNum DECIMAL(8,1) NOT NULL,
ColorName VARCHAR(30) NOT NULL,
Orderqty INT NOT NULL
);
CREATE TABLE Table2 (
ID INT IDENTITY(1,1) PRIMARY KEY,
StyleID INT NOT NULL,
ColorName VARCHAR(30),
Material VARCHAR(30)
);
INSERT INTO Table1
(OCStypeID, OCNum, ColorName, Orderqty) VALUES
(220009069, 106307.1, 'BLACK', 200)
, (220009069, 106307.1, 'BLUE', 200)
, (220009069, 106307.1, 'BROWN', 4)
, (220009069, 106307.1, 'DARK GREEN', 240)
, (220009069, 106307.1, 'WHITE', 20)
5 rows affected
INSERT INTO Table2
(StyleID, ColorName, Material) VALUES
(220009069, 'BLACK', 'Fabric-(ITL-FC)')
, (220009069, 'BLUE', 'Fabric-(ITL-FC)')
, (220009069, 'DARK GREEN', 'Fabric-(ITL-FC)')
, (220009069, 'WHITE', 'Fabric-(ITL-FC)')
4 rows affected
SELECT DISTINCT
t1.OCStypeID
, t1.OCNum
, t1.ColorName
, t1.Orderqty
, COALESCE(t2.StyleID, t1.OCStypeID) AS "StyleID"
, COALESCE(t2.ColorName, t1.ColorName) AS "ColorName"
, COALESCE(t2.Material, '') AS "Material"
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t2.StyleID = t1.OCStypeID
AND t2.ColorName = t1.ColorName
| OCStypeID | OCNum | ColorName | Orderqty | StyleID | ColorName | Material |
|---|---|---|---|---|---|---|
| 220009069 | 106307.1 | BLACK | 200 | 220009069 | BLACK | Fabric-(ITL-FC) |
| 220009069 | 106307.1 | BLUE | 200 | 220009069 | BLUE | Fabric-(ITL-FC) |
| 220009069 | 106307.1 | BROWN | 4 | 220009069 | BROWN | |
| 220009069 | 106307.1 | DARK GREEN | 240 | 220009069 | DARK GREEN | Fabric-(ITL-FC) |
| 220009069 | 106307.1 | WHITE | 20 | 220009069 | WHITE | Fabric-(ITL-FC) |