From mboxrd@z Thu Jan 1 00:00:00 1970 From: dmg Subject: wishful thinking: using SQL to process tables. in the meantime, use R Date: Wed, 20 Jul 2016 18:13:53 -0700 Message-ID: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary=94eb2c04857663933d05381b0aba Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:53907) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1bQ2ZP-0001iW-Ga for emacs-orgmode@gnu.org; Wed, 20 Jul 2016 21:14:36 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1bQ2ZO-0005JP-3Y for emacs-orgmode@gnu.org; Wed, 20 Jul 2016 21:14:35 -0400 Received: from mail-vk0-x22e.google.com ([2607:f8b0:400c:c05::22e]:33811) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1bQ2ZN-0005JK-Tf for emacs-orgmode@gnu.org; Wed, 20 Jul 2016 21:14:34 -0400 Received: by mail-vk0-x22e.google.com with SMTP id s189so92673597vkh.1 for ; Wed, 20 Jul 2016 18:14:33 -0700 (PDT) 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 --94eb2c04857663933d05381b0aba Content-Type: text/plain; charset=UTF-8 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=table1 b=table2 :colnames yes 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), 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=table1 b=table2 :results value :colnames yes merge(a,b,by.x="column") #+END_SRC merge can do left joins, right joins, full joins, joins, https://stat.ethz.ch/R-manual/R-devel/library/base/html/merge.html but there is nothing like the power of SQL to process tables, though. -- --dmg --- Daniel M. German http://turingmachine.org --94eb2c04857663933d05381b0aba Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi everybody,
<= br>
I was wondering if there was a way to join two = tables given
a common column. I searched but found = nothing.

it would be a= wesome to be able to process tables in SQLITE.
some= thing like this:

#+BEG= IN_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 har= d. The infrastructure is there already.
It is just a matter of cr= eating temp tables (this is the major part missing
which implies = making a create statement from the table, but given
that sqlite i= s 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 nee= ded
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 b= e 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 l= ike the power of SQL to process tables, though.

=
--
--dmg

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