From mboxrd@z Thu Jan 1 00:00:00 1970 From: Thierry Banel Subject: Re: wishful thinking: using SQL to process tables. in the meantime, use R Date: Thu, 21 Jul 2016 19:35:02 +0200 Message-ID: <579107C6.5060500@free.fr> References: Mime-Version: 1.0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:33231) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1bQHsJ-0003As-CC for emacs-orgmode@gnu.org; Thu, 21 Jul 2016 13:35:08 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1bQHsI-0006DW-0g for emacs-orgmode@gnu.org; Thu, 21 Jul 2016 13:35:07 -0400 Received: from smtp4-g21.free.fr ([2a01:e0c:1:1599::13]:22753) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1bQHsH-0006D5-NY for emacs-orgmode@gnu.org; Thu, 21 Jul 2016 13:35:05 -0400 Received: from [IPv6:2a01:e35:2e21:def0:86d:f4d4:bdfb:8bb3] (unknown [IPv6:2a01:e35:2e21:def0:86d:f4d4:bdfb:8bb3]) by smtp4-g21.free.fr (Postfix) with ESMTP id 1879E19F598 for ; Thu, 21 Jul 2016 19:37:16 +0200 (CEST) In-Reply-To: List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org
Le 21/07/2016 03:13, dmg a =C3=A9crit=C2= =A0:
Hi everybody,

I was wondering if there was a way to join two tables given
a common column. I searched but found nothing.

it would be awesome to be able to process tables in SQLITE.
something like this:

#+BEGIN_SRC sqlite :var a=3Dtable1 b=3Dtable2 =C2=A0:colnames yes
=C2=A0 select * from $a join $b using column;
#+END_SRC

I think it is not that hard. The infrastructure is there already.
It is just a matter of creating temp tables (this is the major part missing
which implies making a create statement from the table, but given
that sqlite is very type agnostic, it might not be hard),=C2=A0
load them from the CSV files
the execute the block. A db parameter might be needed
for a scratch database file, but it could be a temporary one if
none is provided.

But in the meantime, it occurred to me, it is simple in R to do the join
and might be useful to others:

#+BEGIN_SRC R :var a=3Dtable1 b=3Dtable2 :results value :colnames yes
merge(a,b,by.x=3D"column")
#+END_SRC

merge can do left joins, right joins, full joins, joins,=C2=A0

but there is nothing like the power of SQL to process tables, though.

--
--dmg

---
Daniel M. German
http://turingmachine.org

You may want to try orgtbl-join
=C2=A0 https://github.com/tbanel/orgtbljoin
Available on Melpa (M-x package-install orgtbl-join)
It is pure Emacs (no external dependencies)