# JuliaDB: select columns from 2 tables and join based on 2 keys

**URL:** <https://discourse.julialang.org/t/juliadb-select-columns-from-2-tables-and-join-based-on-2-keys/45076>\
**Category:** General Usage\
**Created:** [August 17, 2020, 4:46am UTC](https://discourse.julialang.org/t/juliadb-select-columns-from-2-tables-and-join-based-on-2-keys/45076 "2020-08-17T04:46:23Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![DShiu](https://avatars.discourse-cdn.com/v4/letter/d/8e8cbc/32.png) [@DShiu](https://discourse.julialang.org/u/DShiu)\
**Post date:** [August 17, 2020, 4:46am UTC](https://discourse.julialang.org/t/juliadb-select-columns-from-2-tables-and-join-based-on-2-keys/45076/1 "2020-08-17T04:46:23Z")

</div>

Suppose that we have 2 tables, each contains multiple columns. The first table has (id, date, x1, x2, x3) and the second has (id, date, y1, y2, y3). Is it possible to create a new table by selecting columns (id, date, x1, y2, y3) from the 2 original tables and inner joining them on (id, date)?

The reason I am asking this is that this procedure is very convenient in SQL. I wish there is a similar thing in JuliaDB.

---

<div class="post-metadata">

**Author:** ![derekmahar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/derekmahar/32/216439_2.png) [@derekmahar](https://discourse.julialang.org/u/derekmahar)\
**Post date:** [August 17, 2020, 9:16am UTC](https://discourse.julialang.org/t/juliadb-select-columns-from-2-tables-and-join-based-on-2-keys/45076/2 "2020-08-17T09:16:35Z")

</div>

More generally, does JuliaDB support arbitrary join conditions as does SQL with the `JOIN <table> ON <condition>` clause?

---

<div class="post-metadata">

**Author:** ![joshday](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/joshday/32/368_2.png) [@joshday](https://discourse.julialang.org/u/joshday)\
**Post date:** [August 17, 2020, 12:48pm UTC](https://discourse.julialang.org/t/juliadb-select-columns-from-2-tables-and-join-based-on-2-keys/45076/3 "2020-08-17T12:48:01Z")

</div>

[API · JuliaDB.jl](https://juliadata.github.io/JuliaDB.jl/latest/api/#Base.join-Tuple%7BAny,Union%7BIndexedTable,%20NDSparse%7D,Union%7BIndexedTable,%20NDSparse%7D)}

I think you want something like:

```julia
join(t1, t2, lkey=(:id, :date), rkey=(:id, :date), lselect=:x1, rselect=(:y2, :y3))

```

You can leave out `lkey` and `rkey` if you already have primary keys set.

---

<div class="post-metadata">

**Author:** ![DShiu](https://avatars.discourse-cdn.com/v4/letter/d/8e8cbc/32.png) [@DShiu](https://discourse.julialang.org/u/DShiu)\
**Post date:** [August 17, 2020, 3:47pm UTC](https://discourse.julialang.org/t/juliadb-select-columns-from-2-tables-and-join-based-on-2-keys/45076/4 "2020-08-17T15:47:42Z")

</div>

This looks good!  
In addition, is it possible to apply “where” and “group by” clauses in the join? This would allow us to use filtered tables and calculate group-level statistics. For example, I am looking for a Julia counterpart for the following SQL command:

```julia
create table want as
select a.x1, b.y2, b.y3, mean(b.y2) as y2_mean
from t1 (where = (x2 > 0)) as a
left join t2 as b
on a.id = b.id and a.date = b.date
group by b.id
order by id, date

```