Re: [sqlite] Query on multiple tables
Martin Gagnon wrote: Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon Martin, Pardon me if I missed something, but if your query only returns fields from table tbl1 (as it says in your comment), why do you need to join with the other tables? Your query doesn't reference fields from the other tables in where clauses that might restrict the rows that are returned, it only gives the join conditions. From what you have said, this should equivalent to your query; Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1 where tbl1.ID=4 Dennis Cote
RE: [sqlite] Query on multiple tables
Hi Marc-André, That's a much better way of doing this. Thanks! Martin P.S. Tes salutations sont rendues! -Original Message- From: Marc-Andre Gosselin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 16, 2005 19:47 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query on multiple tables Hi Martin, You should try to use a JOIN instead of the WHERE clause. Like this : SELECT tbl1.ID, tbl1.fld1, tbl1.fld2 ... FROM tbl1 JOIN tbl2 ON tbl2.ParentID = tbl1.ID JOIN tbl3 ON tbl3.ParentID = tbl2.ID WHERE tbl1.ID = 4 Try that and tell me if that work for you. I've got a similar problem and that's how I was able to make it work. Probably it has something to do with the way SQLite parse the request. Regards Marc-Andre Gosselin P.S.: Si tu es du même bureau tu diras salut à Sébastien Rancourt de ma part! Martin Gagnon a écrit : > Hi all, > Using sqlite3 on QNX 6.3.0. > I need to do a select query on 3 tables by binding them by their ID's. > Something like: > Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from > tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID > AND tbl2.ID=tbl3.ParentID > This returns the expected row instantly but the sqlite3 process takes > more that 10 seconds to give back a prompt, taking all the CPU time. > Is there a way to accomplish this task better? > Thank you, > Martin Gagnon > > >
Re: [sqlite] Query on multiple tables
Hi Martin, You should try to use a JOIN instead of the WHERE clause. Like this : SELECT tbl1.ID, tbl1.fld1, tbl1.fld2 ... FROM tbl1 JOIN tbl2 ON tbl2.ParentID = tbl1.ID JOIN tbl3 ON tbl3.ParentID = tbl2.ID WHERE tbl1.ID = 4 Try that and tell me if that work for you. I've got a similar problem and that's how I was able to make it work. Probably it has something to do with the way SQLite parse the request. Regards Marc-Andre Gosselin P.S.: Si tu es du même bureau tu diras salut à Sébastien Rancourt de ma part! Martin Gagnon a écrit : Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon
[sqlite] Query on multiple tables
Hi all, Using sqlite3 on QNX 6.3.0. I need to do a select query on 3 tables by binding them by their ID's. Something like: Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1, tbl2, tbl3, where tbl1.ID=4 AND tbl1.ID=tbl2.ParentID AND tbl2.ID=tbl3.ParentID This returns the expected row instantly but the sqlite3 process takes more that 10 seconds to give back a prompt, taking all the CPU time. Is there a way to accomplish this task better? Thank you, Martin Gagnon
