422 lines
9.3 KiB
Groff
422 lines
9.3 KiB
Groff
.TH jointable "" "" "/rdb Command"
|
|
.PC "Join two tables into one where keys match"
|
|
\fBjointable [\-a[1|2]] [\-j[1|2] \fIcolumn\fB]... \-n \-] \fItable1 \fB[\fItable2\fB]\fR
|
|
.PP
|
|
.II "table^join two t."
|
|
.II "join tables"
|
|
The command
|
|
.B jointable
|
|
joins
|
|
.I table1
|
|
to
|
|
.I table2
|
|
on
|
|
.IR column ,
|
|
which is common to both tables (called the \fIkey column\fR).
|
|
If no
|
|
.I column
|
|
is named,
|
|
.B jointable
|
|
assumes that the first column in each table is the key column.
|
|
.PP
|
|
Each table must be sorted on the key column.
|
|
.B jointable
|
|
writes a new table that has one row for the rows in the tables
|
|
whose values in the key column match.
|
|
The columns of the new table are all the columns in
|
|
.I table1
|
|
followed by all the columns in
|
|
.IR table2 \(em
|
|
with the sole exception that the key column is not repeated.
|
|
.PP
|
|
This is called a
|
|
.IR "natural join" .
|
|
An
|
|
.I equi-join
|
|
repeats the key column.
|
|
.PP
|
|
The COHERENT system's command
|
|
.B join
|
|
will also join two tables in the manner described above, but
|
|
it does not know about
|
|
.B /rdb
|
|
head lines.
|
|
.SH "String vs. Numeric Sort"
|
|
The commonest problem seen with
|
|
.B jointable
|
|
is when users attempt to join tables that have been sorted
|
|
.I numerically
|
|
on the key columns (that is, the columns
|
|
that the tables are going to be joined on).
|
|
They use
|
|
.B sorttable
|
|
with the
|
|
.B \-n
|
|
option (for numeric) to sort a table, then call
|
|
.B jointable.
|
|
.BR jointable ,
|
|
however, expects to see a
|
|
.I "string sort"
|
|
fails to join the tables correctly.
|
|
.PP
|
|
The following compares a string sort with a numeric sort:
|
|
.DS
|
|
.ta 0.5iC 2.0iC
|
|
\fIString Numeric\fR
|
|
1 1
|
|
10 2
|
|
2 10
|
|
20 20
|
|
.DE
|
|
.PP
|
|
Can you see why?
|
|
Numeric is the way we count and is obviously correct.
|
|
But string sort looks at the first character, then the second.
|
|
A space sorts ahead of `0', so `1' comes before ``10''.
|
|
More importantly, `1' comes before `2'
|
|
regardless of the characters to the right.
|
|
That is why ``10'' comes before ``2'' followed by a space.
|
|
This problem shows up as empty output from a
|
|
.BR jointable ,
|
|
when you know some of the rows should be joined.
|
|
.PP
|
|
Right-justification makes numeric and string sorts the same.
|
|
The leading spaces help the string sort to sort numbers correctly.
|
|
.PP
|
|
To make it easy for users to avoid this problem,
|
|
.B jointable
|
|
has the option
|
|
.BR \-n ,
|
|
for symmetry with the COHERENT command
|
|
.BR sort .
|
|
.PP
|
|
If you want to join two tables through a numeric key column,
|
|
you must either call the
|
|
.B sorttable
|
|
without its
|
|
.B \-n
|
|
numeric sort option, so that
|
|
it defaults to string sort;
|
|
or if you use the
|
|
.B \-n
|
|
option for
|
|
.BR sorttable ,
|
|
be sure to use it also with
|
|
.BR jointable .
|
|
.SH Options
|
|
.B jointable
|
|
recognizes the following options:
|
|
.IP \fB\-a\fIn\fR
|
|
All or one of the files (1 or 2) is output,
|
|
whether it matches or not.
|
|
In addition to the normal output,
|
|
.B jointable
|
|
produces a line for each unpairable line in file
|
|
.IR n ,
|
|
where
|
|
.I n
|
|
is 1 or 2.
|
|
For example, the command
|
|
.DM
|
|
jointable -a1 ledger chart
|
|
.DE
|
|
.sp \n(pDu
|
|
forces
|
|
.B jointable
|
|
to print every row in table
|
|
.BR ledger ,
|
|
regardless of whether any value in
|
|
.BR ledger 's
|
|
.B Account
|
|
column matches any value in
|
|
.BR chart 's
|
|
.B Account
|
|
column.
|
|
.IP "\fB\-j\fIn column\fR
|
|
Join on the
|
|
.I column
|
|
column in table number
|
|
.I n.
|
|
If
|
|
.I n
|
|
is missing, use the
|
|
.I column
|
|
column in each table.
|
|
For example, the command
|
|
.DM
|
|
jointable -j1 Account -j2 account ledger chart
|
|
.DE
|
|
.sp \n(pDu
|
|
joins tables
|
|
.B ledger
|
|
and
|
|
.B chart
|
|
using the
|
|
.B Account
|
|
column in
|
|
.B ledger
|
|
and the
|
|
.B account
|
|
column in
|
|
.BR chart .
|
|
If no columns are specified with this option,
|
|
.B jointable
|
|
joins the tables by the first column in each.
|
|
Thus, this option lets you join on any column defined
|
|
in either file, even when they have different names and locations.
|
|
.IP \fB-n\fR
|
|
Numeric join.
|
|
This means that table1 and table2 were sorted with the numeric option
|
|
.BR \-n .
|
|
Remember that if you use
|
|
.B \-n
|
|
with the command
|
|
.BR sorttable ,
|
|
you must also use
|
|
it with
|
|
.BR jointable .
|
|
.IP \fB\-\fR
|
|
Read the standard input in place of a table.
|
|
For example, the command
|
|
.DM
|
|
sorttable < journal | jointable - chart
|
|
.DE
|
|
.sp \n(pDu
|
|
means to sort table
|
|
.BR journal ,
|
|
then join it with table
|
|
.B chart
|
|
so that
|
|
.B journal
|
|
is on the left of the output table and
|
|
.B chart
|
|
is on the right.
|
|
.sp \n(pDu
|
|
Note that this option is used only in place of
|
|
.IR table1 .
|
|
If
|
|
.I table2
|
|
is absent from the command line,
|
|
.B jointable
|
|
automatically reads the standard input for that table's data.
|
|
.PP
|
|
Note that most of these options are about
|
|
the same as for the COHERENT command
|
|
.BR join .
|
|
.SH Example
|
|
Consider table
|
|
.BR chart ,
|
|
as follows:
|
|
.DM
|
|
Account Name
|
|
------- ----
|
|
100 Assets
|
|
101 Cash
|
|
111 Accounts Receivable
|
|
111.1 Allowance for Bad Debt
|
|
115 Notes Receivable
|
|
120 Deposits
|
|
130 Parts Inventory
|
|
...
|
|
.DE
|
|
.PP
|
|
and table
|
|
.BR ledger ,
|
|
as follows:
|
|
.DM
|
|
Account Date Debit Credit
|
|
------- ---- ----- ------
|
|
101 820102 25000
|
|
101 820103 5000
|
|
101 820104 15000
|
|
130 820104 30000
|
|
150.1 820103 10000
|
|
201.1 820104 15000
|
|
211.1 820102 25000
|
|
211.2 820103 5000
|
|
.DE
|
|
.PP
|
|
Note that each is sorted in string fashion on its first column.
|
|
To join them, use the command:
|
|
.DM
|
|
jointable ledger chart
|
|
.DE
|
|
.PP
|
|
This joins the tables on the first column in each, and writes the
|
|
following to the standard output:
|
|
.DM
|
|
Account Date Debit Credit Name
|
|
------- ---- ----- ------ ----
|
|
101 820102 25000 Cash
|
|
101 820103 5000 Cash
|
|
101 820104 15000 Cash
|
|
130 820104 30000 Parts Inventory
|
|
150.1 820103 10000 Test equipment
|
|
211.1 820102 25000 Notes Payable - BA
|
|
211.2 820103 5000 Notes Payable - Z Equip
|
|
.DE
|
|
.PP
|
|
As you can see, column
|
|
.B Name
|
|
has been added to the columns of the
|
|
.B ledger
|
|
table, and that column
|
|
.B Account
|
|
is not repeated.
|
|
Also note that
|
|
.B jointable
|
|
wrote only the rows whose values in column
|
|
.B Account
|
|
match.
|
|
If a table has several rows with repeated key values (e.g., the
|
|
three
|
|
.BR 101 s
|
|
in the
|
|
.B Account
|
|
column), the joined table's rows are also repeated (see the three
|
|
.BR Cash s
|
|
in column
|
|
.BR Name ).
|
|
.PP
|
|
If you have a table named
|
|
.B journal
|
|
that is sorted on column
|
|
.BR Account ,
|
|
as follows
|
|
.DM
|
|
Date Account Debit Credit
|
|
---- ------- ----- ------
|
|
820104 101 15000
|
|
820103 101 5000
|
|
820102 101 25000
|
|
820104 130 30000
|
|
820103 150.1 10000
|
|
820104 201.1 15000
|
|
820102 211.1 25000
|
|
820103 211.2 5000
|
|
.DE
|
|
.PP
|
|
you could join it with table
|
|
.B chart
|
|
by typing this command:
|
|
.DM
|
|
jointable -j Account journal chart
|
|
.DE
|
|
.PP
|
|
The following gives
|
|
another way for the same result (because
|
|
.BR chart 's
|
|
join column defaults to column 1):
|
|
.DM
|
|
jointable -j1 Account journal chart
|
|
.DE
|
|
.PP
|
|
Both commands produce the following output:
|
|
.DM
|
|
Date Account Debit Credit Name
|
|
------ ------- ----- ------ ----
|
|
820104 101 15000 Cash
|
|
820103 101 5000 Cash
|
|
820102 101 25000 Cash
|
|
820104 130 30000 Parts Inventory
|
|
820103 150.1 10000 Test equipment
|
|
820102 211.1 25000 Notes Payable - Bank of Amerigold
|
|
820103 211.2 5000 Notes Payable - Z Equipment
|
|
.DE
|
|
.PP
|
|
The
|
|
.B \-a
|
|
option lets you see all of one table whether or not there is a match.
|
|
For example, the command
|
|
.DM
|
|
jointable -a1 ledger chart
|
|
.DE
|
|
.PP
|
|
writes the following:
|
|
.DM
|
|
Account Date Debit Credit Name
|
|
------- ---- ----- ------ ----
|
|
101 820102 15000 Cash
|
|
101 820103 5000 Cash
|
|
101 820104 25000 Cash
|
|
130 820104 30000 Parts Inventory
|
|
150.1 820103 10000 Test equipment
|
|
201.1 820104 15000
|
|
211.1 820102 25000 Notes Payable - Bank of Amerigold
|
|
211.2 820103 5000 Notes Payable - Z Equipment
|
|
.DE
|
|
.PP
|
|
Note that column
|
|
.BR Account 's
|
|
value
|
|
.B 201.1
|
|
in the ledger shows up here, even though it is not in
|
|
.BR chart .
|
|
This shows us that
|
|
.B 201.1
|
|
is an error.
|
|
All account numbers should be in the chart of accounts.
|
|
We should either add
|
|
.B 201.1
|
|
to the chart of accounts, or find
|
|
the correct number in the chart of accounts and replace
|
|
.B 201.1
|
|
in the
|
|
.B journal
|
|
with the correct account number.
|
|
.PP
|
|
If you pipe a file into a
|
|
.BR jointable ,
|
|
you can control which table it will be (first or second) by using the
|
|
dash option.
|
|
For example:
|
|
.DM
|
|
sorttable Account < journal | jointable -j Account - chart
|
|
.DE
|
|
.PP
|
|
This produces the following output:
|
|
.DM
|
|
Date Account Debit Credit Name
|
|
---- ------- ----- ------ ----
|
|
820103 101 5000 Cash
|
|
820104 101 15000 Cash
|
|
820102 101 25000 Cash
|
|
820104 130 30000 Parts Inventory
|
|
820103 150.1 10000 Test equipment
|
|
820102 211.1 25000 Notes Payable - Bank of Amerigold
|
|
820103 211.2 5000 Notes Payable - Z Equipment
|
|
.DE
|
|
.PP
|
|
Note the
|
|
.B \-
|
|
before
|
|
.BR chart .
|
|
It says that the standard input file
|
|
will be file 1 or on the left side of the table.
|
|
.SH "Case of Column Names"
|
|
.I "(Ruby re-creation only.)"
|
|
Column names are case sensitive, as in the original
|
|
.BR /rdb .
|
|
If the environment variable
|
|
.B RDB_ICASE
|
|
is set to 1, upper and lower case are ignored when column names are looked up.
|
|
An exact match always wins;
|
|
if several columns differ only in case, the command stops with an
|
|
.I ambiguous
|
|
error.
|
|
Names that are also
|
|
.B awk
|
|
reserved words or are followed by
|
|
.B (
|
|
are never taken for columns.
|
|
.SH "See Also"
|
|
.B
|
|
column,
|
|
sorttable
|
|
.R
|
|
.br
|
|
COHERENT Lexicon:
|
|
.B
|
|
join
|
|
.R
|