Query on multiple tables

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Query on multiple tables

Martin Gagnon
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


Reply | Threaded
Open this post in threaded view
|

Re: Query on multiple tables

Marc-Andre Gosselin-2
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
>
>
>
Reply | Threaded
Open this post in threaded view
|

RE: Query on multiple tables

Martin Gagnon
In reply to this post by Martin Gagnon
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:[hidden email]]
Sent: Thursday, June 16, 2005 19:47
To: [hidden email]
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
>
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Query on multiple tables

Dennis Cote
In reply to this post by Martin Gagnon
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