0

I have multiple tables I need to join horizontally based on a common entry. For the first sets of tables, I need to join tables that look like:

Table 1 dimension = 1093367x18 and It looks like

ROW # ID TEMPERATURE DESCR ... NUMB
1 32 23 Y ... 23
2 47 54 N ... 24
... ... ... ... ... ...
1,093367 78 12 Y ... 45

Table 2 dimension = 1093367x648

ROW # ID COLOR 1 COLOR 2 ... COLOR 648
1 32 RED BLUE ... GREEN
2 47 BLUE PURPLE ... RED
... ... ... ... ... ...
1,093367 78 YELLOW RED ... BLUE

And I need [Table 1 |Table 2]:

ROW # ID TEMPERATURE DESCR ... NUMB COLOR 1 COLOR 2 ... COLOR 648
1 32 23 Y ... 23 RED BLUE ... GREEN
2 47 54 N ... 24 BLUE PURPLE ... RED
... ... ... ... ... ... ... ... ... ...
1,093367 78 12 Y ... 45 YELLOW RED ... BLUE

Is this possible to do in SQLITE? I have only found solutions in which I would have to type out all 648 columns for table 2. Is this the only way to do this in SQLITE?

forpas
  • 160,666
  • 10
  • 38
  • 76
anni
  • 83
  • 5
  • The correct answer gives you a Standard SQL statement, that you can use as a subquery to find all column names in your table. Please check: https://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names –  Mar 27 '22 at 14:29
  • Im getting the error : [10:55:23] Error while executing SQL query on database 'my_database': no such table: INFORMATION_SCHEMA.COLUMNS. Do I have to download a package for this? I've googled this to no avail. – anni Mar 27 '22 at 14:55

1 Answers1

0

You don't have to write any column name in the SELECT statement if you do a join with the USING clause instead of the ON clause:

SELECT *
FROM Table1 INNER JOIN Table2
USING(id);

This will return all the columns of the 2 tables (first Table1's rows and then Table2's rows) but the columns used in the USING clause (in this case the column id on which the join is based) will be returned only once.

You can find more about the USING clause in Determination of input data (FROM clause processing).

See a simplified demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Is there a way to do this with more than 2 tables, or should I export the results and add the next table using this same method? – anni Mar 27 '22 at 15:03
  • @anni check this: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=631447b7380caf821b126e2b945be106 – forpas Mar 27 '22 at 15:06