[GRASS-dev] db.join script wanted

[Markus Neteler]

On Wed, Mar 9, 2016 at 8:11 PM, Paulo van Breugel
<p.vanbreugel at gmail.com> wrote:
> On Wed, Mar 9, 2016 at 7:14 PM, Luca Delucchi <lucadeluge at gmail.com> wrote:
>>
>> On 9 March 2016 at 19:11, Markus Neteler <neteler at osgeo.org> 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