Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #5272

Database "outer join" in Mathematica

From Mark Coleman <markspcoleman@gmail.com>
Newsgroups comp.soft-sys.math.mathematica
Subject Database "outer join" in Mathematica
Date 2011-09-29 06:07 +0000
Organization Steven M. Christensen and Associates, Inc and MathTensor, Inc.
Message-ID <j611vs$m1r$1@smc.vnet.net> (permalink)

Show all headers | View raw


Greetings,

I've struggled with building a general function in Mathematica that performs
an "outer join" similar to that performed in any standard database
system. As an example, consider the following two lists:

x1 = {{"dog", "cat", 1}, {"cow", "sheep", 2}, {"calf", "lamb",
   2}, {"pig", "horse", "x"}, {"lion", "tiger", "y"}}

x2 = {{2, "lemur", "goat"}, {3, "llama", "donkey"}, {"x", "cat",
   "mouse"}}


A standard database-style "outer join", that uses the last column of
x1 and the first column of x2 as the key field (note, these locations
are arbitrary) would return something like

x1x2OuterJoin = {{1, "dog", "cat", "", ""}, {2, "cow", "sheep",
   "lemur", "goat"}, {2, "calf", "lamb", "lemur", "goat"}, {3, "", "",
    "llama", "donkey"}, {"x", "pig", "horse", "cat", "mouse"}, {"y",
   "lion", "tiger", "", ""}}

That is, all rows of both lists occur, including those rows where
there are no match of the key field.

Ideally a very general outer join function, again as implemented in
most common database systems, would allow for keys in any location in
either of the lists, key values that are string or numeric, key values
that are not unique, joining on multiple keys, joining multiple
tables, etc. I searched through the Group archives and from time-to-
time, a question has touched on some aspect of this problem, but none
of the proposed solutions seem general enough. So I thought I post the
general case directly.

Any help or insights would be much appreciated.

Cheers,

Mark

Back to comp.soft-sys.math.mathematica | Previous | Next — Next in thread | Find similar | Unroll thread


Thread

Database "outer join" in Mathematica Mark Coleman <markspcoleman@gmail.com> - 2011-09-29 06:07 +0000
  Re: Database "outer join" in Mathematica Armand Tamzarian <mike.honeychurch@gmail.com> - 2011-09-30 08:05 +0000

csiph-web