Markus Neteler wrote > On Wed, Mar 9, 2016 at 8:11 PM, Paulo van Breugel > <
> p.vanbreugel@ > > wrote: >> On Wed, Mar 9, 2016 at 7:14 PM, Luca Delucchi < > lucadeluge@ > > wrote: >>> >>> On 9 March 2016 at 19:11, Markus Neteler < > neteler@ > > wrote: >>> > >>> > >>> > Yeah.. I think >>> > LEFT OUTER JOIN >>> > must be used, at least with sqlite backend. >> >> >> The code now assumes a 1:1 relationship, is that right? >> >> so if Table 1 >> A|B >> 1|2 >> 2|2 >> >> and Table 2 >> A|C >> 1|3 >> 1|4 >> 2|6 >> >> After joining Table 1 looks like >> >> A|B|C >> 1|2|3 >> 2|2|6 >> >> If correct, it might be good to clearly explain this in the >> documentation? I >> wouldn't mind doing that if a final version is ready. At some point it >> might >> be a nice feature to have the option to select the kind of relationship. > > Yes - with a flag that could be solved or better a "method" parameter. > > Consider this SQLite example: JOIN versus LEFT OUTER JOIN: > > ######## > # Preparation > sqlite3 joinexample.db > > # create first table holding data: > CREATE TABLE data (year, month, day, value); > INSERT INTO data VALUES(2003,10,12,12); > INSERT INTO data VALUES(2003,11,24,10); > INSERT INTO data VALUES(2003,11,25,10); > INSERT INTO data VALUES(2003,11,26,10.9); > INSERT INTO data VALUES(2003,11,27,11); > INSERT INTO data VALUES(2003,11,28,11.6); > INSERT INTO data VALUES(2003,12,2,8); > > #create second table holding names > CREATE TABLE monthnames (number, month); > INSERT INTO monthnames VALUES(11,'November'); > INSERT INTO monthnames VALUES(12,'December'); > > # check > sqlite> .schema > CREATE TABLE data (year, month, day, value); > CREATE TABLE monthnames (number, month); > > sqlite> SELECT * FROM data; > 2003|10|12|12 > 2003|11|24|10 > 2003|11|25|10 > 2003|11|26|10.9 > 2003|11|27|11 > 2003|11|28|11.6 > 2003|12|2|8 > > sqlite> SELECT * FROM monthnames; > 11|November > 12|December > > ######## > # JOIN: (--> this will omit all non-matching lines!) > sqlite> SELECT data.year, data.month, monthnames.month, data.day, > data.value > FROM data,monthnames > WHERE monthnames.number=data.month; > 2003|11|November|24|10 > 2003|11|November|25|10 > 2003|11|November|26|10.9 > 2003|11|November|27|11 > 2003|11|November|28|11.6 > 2003|12|December|2|8 > > ######## > # LEFT JOIN (sort of): (--> this will keep also non-matching lines) > sqlite> SELECT data.year, data.month, monthnames.month, data.day, > data.value > FROM data LEFT OUTER JOIN monthnames > ON monthnames.number=data.month; > 2003|10||12|12 > 2003|11|November|24|10 > 2003|11|November|25|10 > 2003|11|November|26|10.9 > 2003|11|November|27|11 > 2003|11|November|28|11.6 > 2003|12|December|2|8 > > # save result to table: > sqlite> CREATE TABLE datajoin AS > SELECT data.year, data.month, monthnames.month, data.day, > data.value > FROM data LEFT OUTER JOIN monthnames > ON monthnames.number=data.month; > > sqlite> SELECT * FROM datajoin; > 2003|10||12|12 > 2003|11|November|24|10 > 2003|11|November|25|10 > 2003|11|November|26|10.9 > 2003|11|November|27|11 > 2003|11|November|28|11.6 > 2003|12|December|2|8 > > # Done. > > So, I would like to see both methods supported one day :-) > > Markus > _______________________________________________ > grass-dev mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/grass-dev +1 ----- best regards Helmut -- View this message in context: http://osgeo-org.1560.x6.nabble.com/db-join-script-wanted-tp5254985p5255479.html Sent from the Grass - Dev mailing list archive at Nabble.com. _______________________________________________ grass-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/grass-dev
