Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
When one result row comes in , I want to create an object for each table
(My application map one struct to one table) and put the columns of
respective tables in their individual structs. i,e I would set column 0
and column 1 in result row to the struct of T1 and column 2 and 3 to that
of T2 and so on.
and I set pointers between T1 and T2 , T2 and T3.
When a new row comes in and if the object for that row is already created.
i.e if r11 again comes as output, I will not create a new object,instead I
would use the old object and set pointers between the old T1 and object and
the T2 object (if r21 is new,else do not set pointers and do not create
objects)
similarly for T3. Thus,for the above case (the example we discussed), there
will be one pointer from T1 row to T2 row and 3 pointers from T2 row to the
3 T3 rows(one pointer per row).
The end data structure will look like a graph.
I want the API to return this graph./ is there an alternative ?
API call : graph* sqlite3_join(select_stmt*)
Thanks
Prakash
On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch <clem...@ladisch.de>
wrote:
> Prakash Premkumar wrote:
> > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> joining them.
> > The result rows will have 8 columns each.
>
> No. The result will have between 4 and 6 columns, depending on how you
> do the joins.
>
> Example:
>
> CREATE TABLE T1(ID1, Name);
> INSERT INTO "T1" VALUES('r11',NULL);
> CREATE TABLE T2(ID2, ID1);
> INSERT INTO "T2" VALUES('r21','r11');
> CREATE TABLE T3(ID3, ID2);
> INSERT INTO "T3" VALUES('r31','r21');
> INSERT INTO "T3" VALUES('r32','r21');
> INSERT INTO "T3" VALUES('r33','r21');
>
> SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
>
> ID1 Name ID2 ID3
> ---------- ---------- ---------- ----------
> r11 r21 r31
> r11 r21 r32
> r11 r21 r33
>
> > sqlite produces 3 result rows , but I would like to produce one result
> row
> > where the results are linked and the rows r11 and r21 are not
> repeated.(It
> > is similar to an adjacency list representation)
>
> The output of an SQLite query always has a fixed number of columns, and
> for specific data, a fixed number of rows.
>
> Please specify exactly how the output for this example data should look
> like.
>
> > The efficiency I gain with the 2nd approach, is the reduction is in the
> > number of copies .i.e each row is copied only once.
>
> Your program has to read either the value of ID1, to compare it with the
> last one, or some other indication that there is a new T1 row. What's
> the difference? Or how exactly should the API look like?
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
