SQL Server 2022

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

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