Re: [sqlite] LEFT JOIN: ON vs. WHERE

which I thought illustrated Keith’s point. From: sqlite-users on behalf of Allen, Marc Sent: Monday, October 28, 2019 5:27:57 PM To: SQLite mailing list Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE Sorry for top posting.. I'm way too lazy to figure out

Re: [sqlite] LEFT JOIN: ON vs. WHERE

Sorry for top posting.. I'm way too lazy to figure out how to make Outlook play nicely. You say: FROM R LEFT OUTER JOIN S ON R.key = S.key AND R.col = 'foo' Here, AND R.col = 'foo' while valid syntax (unfortunately), has no effect. However,

Re: [sqlite] LEFT JOIN: ON vs. WHERE

that. From: sqlite-users on behalf of James K. Lowden Sent: Monday, October 28, 2019 10:32:21 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE On Mon, 28 Oct 2019 12:10:38 -0600 "Keith Medcalf" wrote: > CREATE TABLE t1 (a,

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Mon, 28 Oct 2019 12:10:38 -0600 "Keith Medcalf" wrote: > CREATE TABLE t1 (a, b); > INSERT INTO t1 VALUES(1,2); > INSERT INTO t1 VALUES(2,3); > INSERT INTO t1 VALUES(1,3); > CREATE TABLE t2 (b, c); > INSERT INTO t2 VALUES(2,3); > INSERT INTO t2 VALUES(3,4); > select * from t1 left join t2 on

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Monday, 28 October, 2019 11:19, James K. Lowden wrote: >When wrestling with this topic, it's useful to remember that ON doesn't >constrain the outer table: > > FROM R LEFT OUTER JOIN S > ON R.key = S.key AND R.col = 'foo' >Here, > AND R.col = 'foo' >while valid syntax

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Sun, 27 Oct 2019 15:09:03 -0700 Benjamin Asher wrote: > My colleagues and I are trying to understand the role of ON > constraints vs WHERE clauses in JOINs. ON applies before JOIN. WHERE applies after. That's a loose interpretation, but IMO it's nevertheless a useful way to think about

Re: [sqlite] LEFT JOIN: ON vs. WHERE

The difference between these two is what happens when a row of A has no matches in B. select * from A left join B on A.Time = B.Time select * from A left join B where A.Time = B.Time In the first one the condition is carried out during the join so if a row of A has no matches in B then the B

Re: [sqlite] LEFT JOIN: ON vs. WHERE

Ah! I see. Thanks for that walk through of OUTER JOIN. Ben > On Oct 27, 2019, at 4:18 PM, Keith Medcalf wrote: > > > On Sunday, 27 October, 2019 16:09, Benjamin Asher > wrote: > >> Is there an advantage to putting WHERE-type filtering in the >> ON constraint vs leaving it in the WHERE

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On Sunday, 27 October, 2019 16:09, Benjamin Asher wrote: > Is there an advantage to putting WHERE-type filtering in the > ON constraint vs leaving it in the WHERE clause for LEFT JOINs? The ON clause is merely syntactic sugar for a parenthesized AND clause appended to the parenthesized WHERE

Re: [sqlite] LEFT JOIN: ON vs. WHERE

This is exactly the kind of advice I was looking for. Thanks again! Ben > On Oct 27, 2019, at 4:04 PM, Simon Slavin wrote: > > On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote: > >> Query A >> >> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE >> tab1.x='constant’; >> >>

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote: > Query A > > SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE > tab1.x='constant’; > > Query B > > SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant' Your use of JOIN here is extremely usual. Depending

Re: [sqlite] LEFT JOIN: ON vs. WHERE

Okay playing with this some more: it seems like since everything is equal to the same constant, it doesn’t really matter? That said, it feels like poor form / not good practice (join can become not useful if you don’t have a constraint joining particular columns). If I want to make it easier on

Re: [sqlite] LEFT JOIN: ON vs. WHERE

Oh you’re right. I realize now I messed up the example. Here are the updated queries: Query A SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE tab1.x='constant’; Query B SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant These return the same results in

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On 27 Oct 2019, at 10:09pm, Benjamin Asher wrote: > SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’; You're doing a JOIN here. How should the engine know which row of tab2 corresponds to which row of tab1 ? Your query is syntactically correct, but it doesn't appear to do anything

Re: [sqlite] LEFT JOIN: ON vs. WHERE

On 10/27/19, Benjamin Asher wrote: > It seems both of the following work, > but I’m not really sure why: I get different answers for the two queries when I try them: CREATE TABLE tab1(x,y); CREATE TABLE tab2(x,y); INSERT INTO tab1 VALUES('constant','string'),('abc','def'),(97,15); INSERT INTO

[sqlite] LEFT JOIN: ON vs. WHERE

Hi there! My colleagues and I are trying to understand the role of ON constraints vs WHERE clauses in JOINs. It seems both of the following work, but I’m not really sure why: Query A: SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’; Query B: SELECT * FROM tab1 LEFT JOIN tab2 ON

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Another reason to do at least the LEFT JOIN + WHERE -> INNER JOIN optimisation: If a view is based on a LEFT JOIN, running a WHERE query on it will exhibit the same poor behavior and here there will be no way to rewrite the query. -- Sent from: http://sqlite.1065341.n5.nabble.com/

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Keith Medcalf wrote > The primary difference is likely the test on b.c. /There was some text here that seems to have been lost and my browser opened a "super resume templates" page... maybe I've been already spectre-hacked? Anyway, I repost:/ The primary difference from what I see is that it

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Keith Medcalf wrote > The primary difference is likely the test on b.c. Keith Medcalf wrote > I think the query is ill-conceived when written as an outer join. If anything, to reflect intentionality the query should have been written as such: SELECT FROM ( SELECT FROM a LEFT JOIN b ) WHERE

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

>To reiterate, Keith: to get the query to execute properly, I didn't >change >the LEFT JOIN to an INNER JOIN! >Nope, >I rewrote > >SELECT >FROM > a > LEFT JOIN b ON >WHERE b.c=5 This means: Take all rows of table a match with rows of table b on the key and where there is no match "fill"

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

To reiterate, Keith: to get the query to execute properly, I didn't change the LEFT JOIN to an INNER JOIN! Nope, I rewrote SELECT FROM a LEFT JOIN b ON WHERE b.c=5 to SELECT FROM a LEFT JOIN b ON AND b.c=5 WHERE b.c IS NOT NULL So I just added a redundant predicate and it runs

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Keith Medcalf wrote > but cannot be re-ordered for OUTER JOINS. Actually, I think order requirements (or rather, row grouping requirements, as far as I can paint it the requirement is just that all outer joined rows come in a bunch for each main join tuple) would not be violated if the join is

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

>Thanks for acknowledging this, you are absolutely right, that's why I >stated >that no DB does perfect optimisations and that computing the >alternative >-OR- based closures are probably much harder to tackle. Also E. Pasma >pointed out the -OR- queries as I wrote them are not really

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

At the risk of preserving this thread well past its end of life cycle... On 2018/01/05 6:58 PM, Dinu wrote: Ryan, You cannot ask SQL a Method query, that's where my whole RDBMS understanding takes me. Everything you ask SQL is underpinned by a specific Method. Perhaps I should have been

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Short sum-up: - The -OR- alternative I provided is not semantically equivalent, I will work on one that is :) - The other one, without the -OR- (second post) still stands. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Richard, Thanks for acknowledging this, you are absolutely right, that's why I stated that no DB does perfect optimisations and that computing the alternative -OR- based closures are probably much harder to tackle. Also E. Pasma pointed out the -OR- queries as I wrote them are not really

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

op 05-01-2018 17:23 schreef David Raymond op david.raym...@tomtom.com: >> Anyway the two queries return the same set of rows. > >> This test also show a small semantic difference in the two queries. >> The set of rows is the same but the second query leaves certain >> details null if only one of

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

> Anyway the two queries return the same set of rows. > This test also show a small semantic difference in the two queries. > The set of rows is the same but the second query leaves certain > details null if only one of the OR conditions is true. That occurs in > row 1. You're

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Dinu wrote: Hi all, I've ran into an optimisation problem with a double-left join that works as an "either" clause. The query is as follows: SELECT * FROM a LEFT JOIN b ON LEFT JOIN c ON WHERE b.someId IN (1,2,3) OR c.someId IN (4,5) This results in a bloated execution plan: SEARCH

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

On 1/4/18, Dinu wrote: > I think it's every DB's intention to optimize as best possible > a query into an execution plan. None does it perfectly, but all try to, very > hard. There are trade-offs here. How much slower are you willing for sqlite3_prepare() to run in order

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

On 2018/01/05 4:24 AM, Dinu wrote: Thank you for your answer, Keith. I had my problem "fixed" before I wrote the first mail. Also with every problem I also provided the fix that worked, for anyone that might run into the same problem. However, it's difficult to not get a little frustrated with

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Thank you for your answer, Keith. I had my problem "fixed" before I wrote the first mail. Also with every problem I also provided the fix that worked, for anyone that might run into the same problem. However, it's difficult to not get a little frustrated with your answer. At

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

l but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dinu >Sent: Thursday, 4 January, 2018 16:01 >To: sqlite-users@mailinglists.sqlite.org >Sub

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Algebrically, having a non-null imperative lookup condition in the WHERE clause means you have a stronger predicate on the same subject (ALL MUST fit vs. ANY that fit). -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

I think they are equivalent, if you look closer. SELECT FROM a LEFT JOIN b ON a.x=b.x WHERE b.y=5 -is- equivalent to SELECT FROM a JOIN b ON a.x=b.x AND b.y=5 SELECT FROM a JOIN b WHERE a.x=b.x AND b.y=5 SELECT FROM a LEFT JOIN b ON a.x=b.x AND b.y=5 WHERE b.y IS NOT NULL All the above are

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

qlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Thursday, January 04, 2018 2:53 PM To: SQLite mailing list Subject: Re: [sqlite] LEFT JOIN + WHERE / OR optimisation They are not semantically equivalent. join conditions attached to an outer join

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

On 2018/01/04 9:28 PM, Dinu wrote: Hi all, I've ran into an optimisation problem with a double-left join that works as an "either" clause. The query is as follows: SELECT * FROM a LEFT JOIN b ON LEFT JOIN c ON WHERE b.someId IN (1,2,3) OR c.someId IN (4,5) This results in a

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

rs- >boun...@mailinglists.sqlite.org] On Behalf Of Dinu >Sent: Thursday, 4 January, 2018 12:29 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] LEFT JOIN + WHERE / OR optimisation > >Hi all, >I've ran into an optimisation problem with a double-left join that

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Probably related: Compound join with a left outer join generates different execution plans: LEFT JOIN ( b JOIN c ON ) WHERE b.something = 5 vs. LEFT JOIN ( b JOIN c ON AND b.something = 5 ) WHERE b.something IS NOT NULL -- Sent from: http://sqlite.1065341.n5.nabble.com/

[sqlite] LEFT JOIN + WHERE / OR optimisation

Hi all, I've ran into an optimisation problem with a double-left join that works as an "either" clause. The query is as follows: SELECT * FROM a LEFT JOIN b ON LEFT JOIN c ON WHERE b.someId IN (1,2,3) OR c.someId IN (4,5) This results in a bloated execution plan: SEARCH a SEARCH

Re: [sqlite] Left join help.

On Fri, 26 Apr 2013 14:06:23 +0200 Clemens Ladisch wrote: > When using CROSS where standard SQL allows it (for an actual cartesian > product), the table ordering does not really matter for optimization > purposes because the DB has to do two nested full table scans anyway.

Re: [sqlite] Left join help.

Many thanks to you all, I really appraciate your helpfulness, Danilo 2013/4/26 Clemens Ladisch > Hick Gunter wrote: > > Actually "CROSS" just forces SQLite to use the tables in the order > > specified. > > This is an SQLite optimization extension. I used CROSS because this

Re: [sqlite] Left join help.

Hick Gunter wrote: > Actually "CROSS" just forces SQLite to use the tables in the order > specified. This is an SQLite optimization extension. I used CROSS because this is the only explicit join type where standard SQL allows to omit the join expression. When using CROSS where standard SQL

Re: [sqlite] Left join help.

2 SEARCH TABLE appx USING INDEX sqlite_autoindex_appx_1 (app_ref_itx=? AND app_ref_sub=?) (~1 rows) -Ursprüngliche Nachricht- Von: Clemens Ladisch [mailto:clem...@ladisch.de] Gesendet: Freitag, 26. April 2013 12:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Left jo

Re: [sqlite] Left join help.

Don't forget to add an ORDER BY clause if you want a specific order. -Ursprüngliche Nachricht- Von: Danilo Cicerone [mailto:cyds...@gmail.com] Gesendet: Freitag, 26. April 2013 11:40 An: SQLITE Forum Betreff: [sqlite] Left join help. Hi to all, I'm looking for a query on the f

Re: [sqlite] Left join help.

Danilo Cicerone wrote: > table appx stores item's(table itx) quantity load for each user (table > subj). I'd to know how many items each user has: > > Paul|Box|3 > Paul|Letter|0 > Paul|Pen|0 > John|Box|0 > John|Letter|4 > John|Pen|0 > > I tried: > > select sub_descr, itx_descr, app_load from subj

[sqlite] Left join help.

Hi to all, I'm looking for a query on the following schema: PRAGMA foreign_keys=ON; BEGIN TRANSACTION; CREATE TABLE subj ( sub_id INTEGER PRIMARY KEY, -- 00 sub_descr TEXT DEFAULT NULL -- 01 ); INSERT INTO "subj" VALUES(1,'Paul'); INSERT INTO "subj" VALUES(2,'John'); CREATE TABLE itx (

Re: [sqlite] LEFT JOIN optimization

On 09/23/2011 04:01 AM, Mira Suk wrote: EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) selectidorderfromdetail 000SCAN

Re: [sqlite] LEFT JOIN optimization

_ > Od: "Jim Morris" > Komu: > Datum: 22.09.2011 23:06 > Předmět: Re: [sqlite] LEFT JOIN optimization > > Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is > an associa

Re: [sqlite] LEFT JOIN optimization

Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is an associated ItemsME record so the left outer join is pointless. Just use the inner join. Normally the left outer join would include all of ItemsME_Properties, that probably explains the table scan.

[sqlite] LEFT JOIN optimization

EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106)   selectid    order    from    detail 0    0    0    SCAN TABLE ItemsME_Properties (~100 rows)

Re: [sqlite] Left Join Issue

> > SELECT > CallsList.NUMBER,ContactList.LAST_NAME,CallsList.DATE_AND_TIME,CallsList.READ_STATUS,LineSettingsList.LINE_NAME,CallsList.LINE_ID,CallsList.NUM_OF_CALLS > FROM CallsList WHERE NUMBER<='31'AND CALL_TYPE=0 COLLATE NOCASE LEFT JOIN > ContactList ON

[sqlite] Left Join Issue

Hi All, I am facing syntax error on Left join with my sqllite setup. The schema and query are given below with error CREATE TABLE CallsList ( ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,NUMBER TEXT NOT NULL,DATE_AND_TIME INTEGER NOT NULL,LINE_ID INTEGER NOT NULL,NUM_OF_CALLS

Re: [sqlite] left join using where clause instead of using on clause

On Mon, Oct 05, 2009 at 02:02:51PM +0200, Tim Lind scratched on the wall: > Hi > > I have a query that is using a left join, with a where clause, and the > results I expect are not returned because the one table doesn't have a > related record. > If I put the constraint in the on clause of

Re: [sqlite] left join using where clause instead of using on clause

On 5 Oct 2009, at 1:02pm, Tim Lind wrote: > I have a query that is using a left join, with a where clause, and the > results I expect are not returned because the one table doesn't have a > related record. > If I put the constraint in the on clause of the query instead, the > expected results

Re: [sqlite] left join using where clause instead of using on clause

Tim Lind wrote: > I have a query that is using a left join, with a where clause, and the > results I expect are not returned because the one table doesn't have a > related record. > If I put the constraint in the on clause of the query instead, the > expected results are returned with the null

Re: [sqlite] left join using where clause instead of using on clause

> That said, constraints go in the WHERE clause. JOIN conditions go in > the JOIN clause. Not necessarily. My personal thinking was that it doesn't matter where you put your join conditions - in WHERE clause or in JOIN clause. And I've always put these condition into WHERE clause because it was

Re: [sqlite] left join using where clause instead of using on clause

On Mon, Oct 5, 2009 at 7:02 AM, Tim Lind wrote: > Hi > > I have a query that is using a left join, with a where clause, and the > results I expect are not returned because the one table doesn't have a > related record. > If I put the constraint in the on clause of the query

[sqlite] left join using where clause instead of using on clause

Hi I have a query that is using a left join, with a where clause, and the results I expect are not returned because the one table doesn't have a related record. If I put the constraint in the on clause of the query instead, the expected results are returned with the null record of the

Re: [sqlite] left join does not return data if right side table is empty

On Feb 3, 2009, at 12:16 PM, Nathan Biggs wrote: > I have a question about version 3.6.10. > > I downloaded the amalgamation source code and built a static > library. I > have included the static library in my Windows console application. I > have noticed something very strange though. to

[sqlite] left join does not return data if right side table is empty

I have a question about version 3.6.10. I downloaded the amalgamation source code and built a static library. I have included the static library in my Windows console application. I have noticed something very strange though. to execute my statement I use the prepare and then step not

Re: [sqlite] Left Join

It's working, Thank you! Igor Tandetnik wrote: > > "Tommy Anderson" <[EMAIL PROTECTED]> > wrote in message news:[EMAIL PROTECTED] >> Select * From ExcelMapValue >> >> OUTER JOIN (SELECT ProjectId, InputId, DataValue, Formula >> FROM ProjectData >> WHERE (ProjectId = @Id))

Re: [sqlite] Left Join

"Tommy Anderson" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Select * From ExcelMapValue > > OUTER JOIN (SELECT ProjectId, InputId, DataValue, Formula > FROM ProjectData > WHERE (ProjectId = @Id)) derivedtbl_1 > > On ExcelMapValue.InputId =

[sqlite] Left Join

Hi, I have a left join problem with SQLite. this Select SELECT ProjectId, InputId, DataValue, Formula FROM ProjectData WHERE (ProjectId = @Id) contains almost 15 000 rows. this Table ExcelMapValue contains almost 5 000 rows. and contains these columns InputId, sheetCode,

Re: [sqlite] Left Join help

- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Wednesday, April 09, 2008 6:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Left Join help Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better. Can yo

Re: [sqlite] Left Join help

Andy Smith wrote: > > With all the changes that got me down to 4.8 seconds. Better. Can you post the exact query that ran in 4.8 seconds? Did you set case_sensitive_like on before you ran the query? > We are > also trying a schema in which we break up the container so it's a bit > smaller.

Re: [sqlite] Left Join help

ATE INDEX extension_keyvalue ON extension(keyvalue); CREATE INDEX extension_mediaitemID ON extension(mediaitemID); CREATE INDEX mediaAudio_artistID ON mediaAudio(artistID); CREATE INDEX mediaAudio_avgRating ON mediaAudio(avgRating); CREATE INDEX mediaAudio_containerID ON mediaAu

Re: [sqlite] Left Join help

Andy Smith wrote: > I have to get this down to < 1 sec. I have 50 queries that are all > formatted similar. We have created another schema where the container > is split into smaller tables which might help in speeding this up. From > what I see the Left joins are killing the speed. > I don't

Re: [sqlite] Left Join help

pe); CREATE INDEX extend_contact ON extend(contact); CREATE INDEX extend_titleON extend(title); CREATE INDEX extension_containerID ON extension(containerID); CREATE INDEX extension_extendID ON extension(extendID); CREATE INDEX extension_keyvalue ON extension(keyvalue); C

Re: [sqlite] Left Join help

ON container(type); CREATE INDEX extend_contact ON extend(contact); CREATE INDEX extend_titleON extend(title); CREATE INDEX extension_containerID ON extension(containerID); CREATE INDEX extension_extendID ON extension(extendID); CREATE INDEX extension_keyvalue ON extension(k

Re: [sqlite] Left Join help

Andy Smith wrote: > I have quiet a few queries similar to this doing multiple Left Joins and > they run extremely slow > 6 secs. Is there a better way to be writing > the below query for sqlite. > Andy, You didn't show your table and index definitions, so it's hard to be sure about what would

Re: [sqlite] Left Join help

Andy Smith <[EMAIL PROTECTED]> wrote: > I have quiet a few queries similar to this doing multiple Left Joins > and > they run extremely slow > 6 secs. Is there a better way to be writing > the below query for sqlite. > > > > SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, >

[sqlite] Left Join help

I have quiet a few queries similar to this doing multiple Left Joins and they run extremely slow > 6 secs. Is there a better way to be writing the below query for sqlite. SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID, a.mimeType, a.width, a.height, a.genreID,

[sqlite] left join usage and performance

First of all I must say that I enjoyed reading your presentation at http://www.sqlite.org/php2004/page-001.html on the internals of SQLite and the performance tips. I could not find any information on the processing of LEFT JOIN queries though... As SQLite only does loop-joins, I wonder how I

Re: [sqlite] LEFT JOIN doesn't work as expected

Right. I'm sorry. I should get rid of this CC'ing habit ;-) Rolf D. Richard Hipp wrote: [EMAIL PROTECTED] wrote: P.S.: I have put Mr. Hipp on CC because after many hours of testing and reading up on SQL (that's basically all I did today), I'm quite convinced that there's a bug. I read the

Re: [sqlite] LEFT JOIN doesn't work as expected

[EMAIL PROTECTED] wrote: P.S.: I have put Mr. Hipp on CC because after many hours of testing and reading up on SQL (that's basically all I did today), I'm quite convinced that there's a bug. I read the mailing list -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: Re: [sqlite] LEFT JOIN doesn't work as expected

Klint Gore <[EMAIL PROTECTED]> schrieb am 04.08.2004, 10:30:00: > On Wed, 4 Aug 2004 08:48:01 +0200, wrote: > > Any more ideas on how to get the result I want from SQLite? > > Or is this something that can't be done? > > union the 2 sets of properties together. if you need to add objects >

Re: [sqlite] LEFT JOIN doesn't work as expected

Darren Duncan wrote: > At 8:12 PM +0200 8/3/04, Rolf Schaeuble wrote: >>SELECT >> object.object_id, >> properties.value_table, >> string_values.value, >> int_values.value >> >>FROM object >>LEFT OUTER JOIN properties ON object.object_id = properties.object_id >>LEFT OUTER JOIN

Re: [sqlite] LEFT JOIN doesn't work as expected

At 8:12 PM +0200 8/3/04, Rolf Schaeuble wrote: SELECT object.object_id, properties.value_table, string_values.value, int_values.value FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON

[sqlite] LEFT JOIN doesn't work as expected

Hello, today I've stumbled over a problem that I can't seem to find an answer for. I have the following tables: CREATE TABLE object ( object_id INTEGER PRIMARY KEY ); CREATE TABLE properties ( object_id INTEGER, property_id INTEGER, value_table INTEGER ); CREATE