CODEWITHSUNDEEP
mysqlsqliteselectjoin
Larry Hunsicker
Larry Hunsicker

Reputation: 426

SQLite: join selected columns from two tables

I am trying to create a new SQLite table merging only selected columns from one table with all of the columns from the second table. The two tables share only one column (primary key for one table and foreign key for the second table).

create temp tablex as select column 1, column2 from table1 natural join table2;

The join runs, but the resulting table has only the columns from table 1 -- none of the columns from table 2. I have tried using a subquery for table2:

create temp tablex as select column 1, column2 from table1 natural join select * from table2;

or

create temp tablex as select column 1, column2 from table1 natural join (select * from table2);

(and a few more stabs at getting it right), but these all give me a syntax error "near select." I can do the merge fine if I create the column subset of the table1 ahead of time and do a natural merge of the resulting two tables without any "selects." But I think that I should be able to do this in a single SQL statement. What am I doing wrong? (I can create a model if necessary, but I assume that I have just missed a point in correct syntax). Thanks in advance for your help. Larry Hunsicker

Upvotes: 1

Views: 1441

Answers (2)

Larry Hunsicker
Larry Hunsicker

Reputation: 426

Yes. This works.

create temp table a as select b.col1, b.col2, c.* from table1 as b natural join table2 as c;

Many thanks.

Upvotes: 1

Muhammad Ramzan
Muhammad Ramzan

Reputation: 288

create temp tablex as select table1.column1, table1.column2, table2.column3, table2.column4 from table1, table2 where table1.PrrimaryKey = table2.ForiegnKey;

you may do it like this.

Upvotes: 1

Related Questions