trdsql CROSS JOIN

CROSS JOIN is an easy way to create a total.

a.csv

aa
ab
ac

b.csv

ba
bb
bc

When you CROSS JOIN two CSV files, a 3x3 output is possible for all combinations.

$ trdsql "SELECT * FROM a.csv CROSS JOIN b.csv"
aa,ba
aa,bb
aa,bc
ab,ba
ab,bb
ab,bc
ac,ba
ac,bb
ac,bc

You can also self-join a single file. For example, let’s create a home and away total table.

cleague.csv

team
Giants
DeNA
Tigers
Carp
Dragons
Swallows

To simply CROSS JOIN, it looks like this (you can’t write JOIN conditions because there are none).

$ trdsql -ih \
"SELECT h.team,a.team "\
"  FROM cleague.csv AS h "\
" CROSS JOIN cleague.csv AS a"

You can’t play against your own team, so exclude the same team with WHERE h.team != a.team.

$ trdsql -ih -omd \
"SELECT h.team AS home,a.team AS aware " \
"  FROM cleague.csv AS h CROSS JOIN cleague.csv AS a "\
" WHERE h.team != a.team "