Joining Files and Tables with trdsql

Posted on:

By connecting to a database where a table already exists, you can also join files and tables.

For example, if there is a table called fruits in the database, you can join it with the previous abc.csv.

$ trdsql -driver postgres -dsn "dbname=trdsql_test" \
     "SELECT a.c1, a.c2, f.name FROM abc.csv AS a "\
  "LEFT JOIN fruits AS f ON (CAST(a.c1 AS int) = f.id)"
1,AAA,Orange
2,BBB,Melon
3,CCC,Apple

For example, if there is a users table on the database and the list you want to extract is a CSV file, you would consider listing it with WHERE user IN (…), but with trdsql, you can directly join and extract it.

list.csv

tarou
jirou
noborus

users table

id,name
1,taizou
2,momo
3,tarou
$ trdsql -driver postgres -dsn "dbname=trdsql_test" \
     "SELECT u.id, u.name FROM users AS u "\
 "INNER JOIN list.csv AS l ON (u.name = l.c1)"
3,tarou

Conversely, it is also possible to add information from a database table to a CSV file.

$ trdsql -driver postgres -dsn "dbname=trdsql_test" \
     "SELECT u.id, u.name FROM list.csv AS l "\
  "LEFT JOIN users AS u ON (l.c1 = u.name)" \
       "ORDER BY u.id"
3,tarou
52,jirou
98,noborus