databasesqlitejoinselectorganization

SQLITE: horizontally combining large sql tables based on common entry

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?

Solution

  • 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.