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