# How to join tables on multiple columns in a loop

**URL:** <https://discourse.getdbt.com/t/how-to-join-tables-on-multiple-columns-in-a-loop/4854>\
**Category:** Archive\
**Tags:** jinja\
**Created:** [September 1, 2022, 5:53am UTC](https://discourse.getdbt.com/t/how-to-join-tables-on-multiple-columns-in-a-loop/4854 "2022-09-01T05:53:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jessel](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jessel](https://discourse.getdbt.com/u/jessel)\
**Post date:** [September 1, 2022, 5:53am UTC](https://discourse.getdbt.com/t/how-to-join-tables-on-multiple-columns-in-a-loop/4854/1 "2022-09-01T05:53:59Z")

</div>

Hi,

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

```auto
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

```auto
    {% 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!

---

<div class="post-metadata">

**Author:** ![tekavec](https://avatars.discourse-cdn.com/v4/letter/t/278dde/32.png) [@tekavec](https://discourse.getdbt.com/u/tekavec)\
**Post date:** [September 2, 2022, 3:10pm UTC](https://discourse.getdbt.com/t/how-to-join-tables-on-multiple-columns-in-a-loop/4854/2 "2022-09-02T15:10:34Z")

</div>

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:

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

```

Cheers,  
Tomaz

---

<div class="post-metadata">

**Author:** ![jessel](https://avatars.discourse-cdn.com/v4/letter/j/4491bb/32.png) [@jessel](https://discourse.getdbt.com/u/jessel)\
**Post date:** [September 4, 2022, 4:41pm UTC](https://discourse.getdbt.com/t/how-to-join-tables-on-multiple-columns-in-a-loop/4854/3 "2022-09-04T16:41:22Z")

</div>

It works. Thank you!

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [September 4, 2022, 10:42pm UTC](https://discourse.getdbt.com/t/how-to-join-tables-on-multiple-columns-in-a-loop/4854/4 "2022-09-04T22:42:58Z")

</div>

> [@tekavec](#):
>
> Have you tried with fixed ‘1=1’ expression in your ON segment and all subsequent column links can start with AND?

This is a good option 👍 you can also take advantage of [`loop.first`](https://jinja.palletsprojects.com/en/3.0.x/templates/#for):

```auto
    {% 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 %}

```