On 2014/09/16 15:32, Paul Sanderson wrote:
select _rowid_, * from tab3 does the trick - thanks all

Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then fill it, some variation on this:

CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name 
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, tab2 C;

tab 3 should now look like this (according to your sample tables):
rowNo|   name   |   country
-----------------------------------------
    1     |  paul       | uk
    2     |  paul       | scotland
    3     |  helen     | uk
    4     |  helen     | scotland
    5     |  melanie | uk
    6     |  melanie | scotland


NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can be thought of as a variation of this query which achieves the same:
INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country FROM 
tab1 N, tab2 C;


Hope that widens your SQL arsenal another micron, Cheers!
Ryan


_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to