# Joining tables with lists of data

**URL:** <https://forum.kx.com/t/joining-tables-with-lists-of-data/12113>\
**Category:** Community Support\
**Tags:** kdb-and-q\
**Created:** [February 22, 2021, 6:20pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113 "2021-02-22T18:20:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![hoffmanroni1](https://avatars.discourse-cdn.com/v4/letter/h/8dc957/32.png) [@hoffmanroni1](https://forum.kx.com/u/hoffmanroni1)\
**Post date:** [February 22, 2021, 6:20pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113/1 "2021-02-22T18:20:00Z")

</div>

Hi All, wondering if someone can help.

I have 3 tables with a column of lists.&nbsp; I want to take the the tables and add them to the first tables list.

So for example

a:([key1:`a`b`c]val:(enlist 1;enlist 2;enlist 3))

b:([key1:`a`b`c]val:(enlist 4;enlist 5;enlist 6))

c:([key1:`a`b`c]val:(enlist 7;enlist 8;enlist 9))

and end result would be&nbsp;

q)key1| val

----| —

a&nbsp; &nbsp;| 1 4 7

b&nbsp; &nbsp;| 2 5 8

c&nbsp; &nbsp;| 3 6 9

I’m sure its something easy here but didn’t see it.&nbsp;

Thanks!

---

<div class="post-metadata">

**Author:** ![zak1](https://avatars.discourse-cdn.com/v4/letter/z/858c86/32.png) [@zak1](https://forum.kx.com/u/zak1)\
**Post date:** [February 22, 2021, 6:23pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113/2 "2021-02-22T18:23:00Z")

</div>

Try this

q),‘’/[(a;b;c)]

key1| val &nbsp;

----| -----

a &nbsp; | 1 4 7

b &nbsp; | 2 5 8

c &nbsp; | 3 6 9

---

<div class="post-metadata">

**Author:** ![sohagan8571](https://avatars.discourse-cdn.com/v4/letter/s/ba9def/32.png) [@sohagan8571](https://forum.kx.com/u/sohagan8571)\
**Post date:** [February 22, 2021, 6:27pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113/3 "2021-02-22T18:27:00Z")

</div>

Hi Roni

(,‘’/)(a;b;c)

Would do the trick for you

Kind regards

Sean

---

<div class="post-metadata">

**Author:** ![cathal\_o\_neill](https://avatars.discourse-cdn.com/v4/letter/c/8c91f0/32.png) [@cathal\_o\_neill](https://forum.kx.com/u/cathal_o_neill)\
**Post date:** [February 22, 2021, 7:10pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113/4 "2021-02-22T19:10:00Z")

</div>

Hello Roni,

This is one way to achieve the result you’re looking for.

q)select val by key1 from raze 0!'(a;b;c)

key1| val

----| -----

a | 1 4 7

b | 2 5 8

c | 3 6 9

Joining keyed tables only leaves the left table values. That is why I unkey with&nbsp;0!&nbsp;before selecting all values by&nbsp;key1.

q)a,c

key1| val

----| —

a | 7

b | 8

c | 9

q)(0!a),0!c

key1 val

--------

a 1

b 2

c 3

a 7

b 8

c 9

Hope this helps,

Cathal

---

<div class="post-metadata">

**Author:** ![dflynch31](https://avatars.discourse-cdn.com/v4/letter/d/45deac/32.png) [@dflynch31](https://forum.kx.com/u/dflynch31)\
**Post date:** [February 22, 2021, 7:42pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113/5 "2021-02-22T19:42:00Z")

</div>

A keyed table is a dictionary mapping between a table of keys and a table of values, so a simple join will have upsert semantics.

```
q)a,b,c
key1| val
----| ---
a | 7
b | 8
c | 9

```

We may apply the join operation between the values of two dictionaries using each. We might ordinarily expect this to work, like:

```
q)(`a`b`c!1 2 3),'(`a`b`c!4 5 6)
a| 1 4
b| 2 5
c| 3 6

```

However, the values of a keyed table are the rows of the value table, which are themselves dictionaries (remember a table is a list of dictionaries), so we still see upsert semantics.

```
q)a,'b,'c
key1| val
----| ---
a | 7
b | 8
c | 9  

```

The key insight is that we need to further map the join-each operation onto the values of these row dictionaries:

```
q)a,''b,''c
key1| val
----| -----
a | 1 4 7
b | 2 5 8
c | 3 6 9

```

And with this, we can use over to apply this join-each-each operation to a list of keyed tables.

```
q),''/[(a;b;c)]
key1| val
----| -----
a | 1 4 7
b | 2 5 8
c | 3 6 9

```

&nbsp;

---

<div class="post-metadata">

**Author:** ![hoffmanroni1](https://avatars.discourse-cdn.com/v4/letter/h/8dc957/32.png) [@hoffmanroni1](https://forum.kx.com/u/hoffmanroni1)\
**Post date:** [February 22, 2021, 8:43pm UTC](https://forum.kx.com/t/joining-tables-with-lists-of-data/12113/6 "2021-02-22T20:43:00Z")

</div>

Thanks very much all, thats helpful.