How to join tables on multiple columns in a loop

Archive
1

Hi,

Iā€™m trying to write a macro to join two tables on multiple columns, like this:

SELECT 
  A.col1,
  A.col2,
  A.col3,
FROM
   A
LEFT JOIN
   B
ON
   (A.col1 = B.col1 OR (IS_NAN(A.col1) AND IS_NAN(B.col1))
   AND (A.col2 = B.col2 OR (IS_NAN(A.col2) AND IS_NAN(B.col2))
   AND (A.col3 = B.col3 OR (IS_NAN(A.col3) AND IS_NAN(B.col3))

and this logic will apply to many table pairs, so need a macro. The joining logic is the same on all columns, so a loop over columns in the ON clause would be perfect, like this

    {% for col in all_cols %}
        (A.{{col}} = B.{{col}} OR (IS_NAN(A.{{col}}) AND IS_NAN(B.{{col}})),
    {% endfor %}

but Iā€™m not sure how to concatenate them with AND when iterating over columns. Can someone help on this macro? Thank you!

2

Hi Jesse,

Have you tried with fixed ā€˜1=1ā€™ expression in your ON segment and all subsequent column links can start with AND?
Something like this:

FROM
   A
LEFT JOIN
   B
ON
  1=1
  {{ <your loop macro> }}

Cheers,
Tomaz

2 Likes
3

It works. Thank you!

1 Like
4

This is a good option :+1: you can also take advantage of loop.first:

    {% for col in all_cols %}
        {% if not loop.first -%} AND {% endif -%}
        (A.{{col}} = B.{{col}} OR (IS_NAN(A.{{col}}) AND IS_NAN(B.{{col}}))
    {% endfor %}
2 Likes