CODEWITHSUNDEEP
sqlcaseapplyouter-apply
TripleCute
TripleCute

Reputation: 83

Using Outer Apply to Compare 2 columns of one table that match values with 1 column in a different table

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.

Upvotes: 0

Views: 111

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

fiddle

Upvotes: 1

Related Questions