Thanks a lot Hick,for your approach.
With the approach you suggested, we are creating extra queries and if the
join is on n tables there will be n+1 queries , and each query will have to
go through a query planning stage.
Is there an alternative idea ?

Thanks a lot
Prakash

On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote:

> You are free to build your own result conversion routine on top of the
> SQLite Api.
>
>
> May I suggest selecting the rowids of the tables too i.e.
>
> SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM <...your
> join...>;
>
> When you first come across a new rowid you can create your memory object
> and populate it from the required fields.
> The disadvantage ist hat SQLite will still retrieve all requested fields
> for each result row.
>
>
> Or maybe you might like to
>
> SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
>
> and then populate your structure by binding the rowid received and
> executing the appropriate
>
> SELECT <...t1 fields> FROM t1 where t1.rowid=?;
> SELECT <...t2 fields> FROM t2 where t2.rowid=?;
> SELECT <...t3 fields> FROM t3 where t3.rowid=?;
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Mittwoch, 24. September 2014 09:32
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Results of Joins in sqlite
>
> 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
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> _______________________________________________
> 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

Reply via email to