[GRASS-dev] db.join script wanted

[Helmut Kudrnovsky]

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

> grass-dev at .osgeo

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