I'm new to APPLY statements in SQL and I'm trying to make a solution using an APPLY statement.
I have two tables:
Original ID | Target ID |
---|---|
SHAPE1 | SHAPE2 |
SHAPE3 | SHAPE4 |
Shape | Color | Num Sides | Size |
---|---|---|---|
SHAPE1 | Orange | 3 | Large |
SHAPE2 | Red | 6 | Small |
SHAPE3 | Orange | 3 | Large |
SHAPE4 | Green | 6 | Small |
These tables describe Shape IDs and a 'target' Shape. The goal here is to determine if the characteristics of Original Shape are the same as Target Shape. The query would compare the two columns and return 'TRUE' if they shared the same characteristics.
So, in the second table-- Shape 1 is a twin of Shape 3 meaning a query should return 'true' for Shape 1 and Shape 3, but not for anything else as there isn't a perfect match. (note the color green/red are different for 2 and 4)
SELECT CASE WHEN
(SELECT COUNT(1)
FROM table2 t2 (NOLOCK)
OUTER APPLY (SELECT t2.size, t2.color, t2.sides
WHERE t1.original_id = t2.shape
) source
OUTER APPLY (SELECT t2.size, t2.color, t2.sides
WHERE t1.target_id = t2.shape
) target
WHERE (ISNULL(source.size, '') <> ISNULL(target.size, '')
OR ISNULL(source.color, '') <> ISNULL(target.color, '')
OR ISNULL(source.sides, '') <> ISNULL(target.sides, '')) > 0
THEN NULL ELSE 'TRUE'
END
This was my attempt to do this as an outer apply. I've made the query work using both an 'EXISTS' and 'COUNT' statement, but I wanted to test my knowledge and see if it was ever possible using APPLYs. Unfortunately, this query doesn't yield correct results. It seems to think no matter what, there's always a difference between Shapes.
Well it is possible to use apply operators, but I sincerely don't believe it is worth the effort when simple joins will do the job. Note for the results below I added a row:
INSERT INTO t1 (OriginalID, TargetID) VALUES
('SHAPE1', 'SHAPE2'),
('SHAPE3', 'SHAPE4'),
('SHAPE1', 'SHAPE3'); -- added
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 |