Joins over two files

[tlehman]

First of all, textql is awesome!

I haven't been able to figure out how to do a JOIN in a single command (if that's even possible), as a concrete example I have two TSV files, one for child mortality by country and another for GDP by country:

→ ls
mort.tsv  gdp.tsv

I can do queries over each file, sorting, selecting, limiting, etc.

→ textql -dlm=tab -header=false -source=gdp.tsv -sql='select c1, c0 from tbl order by cast(replace(c1,",","") as integer) desc LIMIT 5'
16,244,600,000,000, United States
8,227,102,629,831, China
5,959,718,262,199, Japan
3,428,130,624,839, Germany
2,612,878,387,760, France

What I would like to do is an inner join on the two files on the country column.

From looking at the source code, I see that there is one table_name variable, does this mean that only one table is supported?

[dinedal]

At present, yes, I am still working on a version that supports multiple files from the get go.

You can however, do this work with two commands:

textql -source ./sample_data.csv -sql "create table some_other_table as select * from tbl" -save-to ./join_against_me.db
textql -source ./sample_data2.csv -sql "select * from tbl inner join some_other_table on tbl.id = some_other_table.id" -save-to ./join_against_me.db

The database given in -save-to is opened and added to, not overwritten or deleted. But the insert always occurs.

I do fully plan to support the multiple file join in one command soon.

[tlehman]

Nice solution!

For the future multiple source file version, would the syntax look something like:

textql -source file1.csv file2.csv -sql 'select tbl0.c0, tbl1.c3 from tbl0 join tbl1 on tbl0.c1 = tbl1.c1

?

[nicolai86]

Any progress on this issue? Would be awesome to query/ join multiple csv files :+1:

[dinedal]

Released as part of V2!

[tlehman]

:+1: