By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1 (
OriginalID VARCHAR(50),
TargetID VARCHAR(50)
);
CREATE TABLE t2 (
Shape VARCHAR(50),
Color VARCHAR(50),
NumSides INT,
Size VARCHAR(50)
);
INSERT INTO t1 (OriginalID, TargetID) VALUES
('SHAPE1', 'SHAPE2'),
('SHAPE3', 'SHAPE4'),
('SHAPE1', 'SHAPE3');
INSERT INTO t2 (Shape, Color, NumSides, Size) VALUES
('SHAPE1', 'Orange', 3, 'Large'),
('SHAPE2', 'Red', 6, 'Small'),
('SHAPE3', 'Orange', 3, 'Large'),
('SHAPE4', 'Green', 6, 'Small');
7 rows affected
SELECT
t1.OriginalID AS OriginalShape
, t1.TargetID AS TargetShape
, CASE WHEN t2.Color = t3.Color
AND t2.NumSides = t3.NumSides
AND t2.Size = t3.Size THEN 'TRUE' ELSE 'FALSE'
END AS SameCharacteristics
, t2.Color t2color
, t3.Color t3color
, t2.NumSides t2NumSides
, t3.NumSides t3NumSides
, t2.Size t2Size
, t3.Size t3Size
FROM t1
CROSS APPLY
(SELECT * FROM t2 WHERE t1.OriginalID = t2.Shape) t2
CROSS APPLY
(SELECT * FROM t2 WHERE t1.TargetID = t2.Shape) t3;
| OriginalShape | TargetShape | SameCharacteristics | t2color | t3color | t2NumSides | t3NumSides | t2Size | t3Size |
|---|---|---|---|---|---|---|---|---|
| SHAPE1 | SHAPE2 | FALSE | Orange | Red | 3 | 6 | Large | Small |
| SHAPE1 | SHAPE3 | TRUE | Orange | Orange | 3 | 3 | Large | Large |
| SHAPE3 | SHAPE4 | FALSE | Orange | Green | 3 | 6 | Large | Small |
SELECT
t1.OriginalID AS OriginalShape
, t1.TargetID AS TargetShape
, CASE WHEN t2.Color = t3.Color
AND t2.NumSides = t3.NumSides
AND t2.Size = t3.Size THEN 'TRUE' ELSE 'FALSE'
END AS SameCharacteristics
, t2.Color t2color
, t3.Color t3color
, t2.NumSides t2NumSides
, t3.NumSides t3NumSides
, t2.Size t2Size
, t3.Size t3Size
FROM t1
JOIN t2 t2 ON t1.OriginalID = t2.Shape
JOIN t2 t3 ON t1.TargetID = t3.Shape
| OriginalShape | TargetShape | SameCharacteristics | t2color | t3color | t2NumSides | t3NumSides | t2Size | t3Size |
|---|---|---|---|---|---|---|---|---|
| SHAPE1 | SHAPE2 | FALSE | Orange | Red | 3 | 6 | Large | Small |
| SHAPE1 | SHAPE3 | TRUE | Orange | Orange | 3 | 3 | Large | Large |
| SHAPE3 | SHAPE4 | FALSE | Orange | Green | 3 | 6 | Large | Small |