4 ms·

If I store data from different sources in the same sqlite db file, is it possible to do joins? I think that there are a lot of use cases if I can join data fro

If I store data from different sources in the same sqlite db file, is it possible to do joins? I think that there are a lot of use cases if I can join data from different sources of my knowledge base

simonw 6y ago
Yes, absolutely. SQLite can even join data together from multiple different database files, though the Datasette interface doesn't support cross database joins just yet.
- BenGosub 6y ago
  Can I use the same database to pull from multiple sources? And Does datasette provide tools to make it easier to do joins on data from different sources?
  - simonw 6y ago
    Yes, you can load data from different sources into the same database - each of the Dogsheep tools lets you pick the database file that you are importing data into. You can create bookmark SQL queries that perform joins in Datasette, or set up views. Here's an example: https://covid-19.datasettes.com/covid?sql=select%0D%0A++++++ny_times_us_counties.date%2C%0D%0A++++++ny_times_us_counties.county%2C%0D%0A++++++ny_times_us_counties.state%2C%0D%0A++++++ny_times_us_counties.fips%2C%0D%0A++++++ny_times_us_counties.cases%2C%0D%0A++++++ny_times_us_counties.deaths%2C%0D%0A++++++us_census_county_populations_2019.population%2C%0D%0A++++++1000000+*+ny_times_us_counties.deaths+%2F+us_census_county_populations_2019.population+as+deaths_per_million%2C%0D%0A++++++1000000+*+ny_times_us_counties.cases+%2F+us_census_county_populations_2019.population+as+cases_per_million%0D%0A++++from%0D%0A++++++ny_times_us_counties%0D%0A++++++join+us_census_county_populations_2019+on+ny_times_us_counties.fips+%3D+us_census_county_populations_2019.fips%0D%0A++++where%0D%0A++++++%22date%22+%3D+%28%0D%0A++++++++select%0D%0A++++++++++max%28date%29%0D%0A++++++++from%0D%0A++++++++++ny_times_us_counties%0D%0A++++++%29%0D%0A++++order+by%0D%0A++++++deaths_per_million+desc%3B https://covid-19.datasettes.com/covid?sql=select%0D%0A++++++...
    - BenGosub 6y ago
      Cool, I'll have a look

If I store data from different sources in the same sqlite db file, is it possible to do joins? I think that there are a lot of use cases if I can join data from different sources of my knowledge base

simonw 6y ago

Yes, absolutely. SQLite can even join data together from multiple different database files, though the Datasette interface doesn't support cross database joins just yet.

BenGosub 6y ago
Can I use the same database to pull from multiple sources? And Does datasette provide tools to make it easier to do joins on data from different sources?
- simonw 6y ago
  Yes, you can load data from different sources into the same database - each of the Dogsheep tools lets you pick the database file that you are importing data into. You can create bookmark SQL queries that perform joins in Datasette, or set up views. Here's an example: https://covid-19.datasettes.com/covid?sql=select%0D%0A++++++ny_times_us_counties.date%2C%0D%0A++++++ny_times_us_counties.county%2C%0D%0A++++++ny_times_us_counties.state%2C%0D%0A++++++ny_times_us_counties.fips%2C%0D%0A++++++ny_times_us_counties.cases%2C%0D%0A++++++ny_times_us_counties.deaths%2C%0D%0A++++++us_census_county_populations_2019.population%2C%0D%0A++++++1000000+*+ny_times_us_counties.deaths+%2F+us_census_county_populations_2019.population+as+deaths_per_million%2C%0D%0A++++++1000000+*+ny_times_us_counties.cases+%2F+us_census_county_populations_2019.population+as+cases_per_million%0D%0A++++from%0D%0A++++++ny_times_us_counties%0D%0A++++++join+us_census_county_populations_2019+on+ny_times_us_counties.fips+%3D+us_census_county_populations_2019.fips%0D%0A++++where%0D%0A++++++%22date%22+%3D+%28%0D%0A++++++++select%0D%0A++++++++++max%28date%29%0D%0A++++++++from%0D%0A++++++++++ny_times_us_counties%0D%0A++++++%29%0D%0A++++order+by%0D%0A++++++deaths_per_million+desc%3B https://covid-19.datasettes.com/covid?sql=select%0D%0A++++++...
  - BenGosub 6y ago
    Cool, I'll have a look