Re: [sqlite] Query on multiple tables

[Marc-Andre Gosselin]

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] Update unique column

[Marc-Andre Gosselin]

Hi,

I discovered a behavior in SQLite 2.8.16 that doesn't conform to the SQL
standard, here's an example :

CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
VARCHAR(100));
INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");

Now when I try the following update, I get a constraint error :

UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;

In the SQL Standard and NIST SQL test suite they say than an update 
should
be considered atomic, and verify unique constraints only after the operation has
updated all rows. From what I experienced with SQLite, constraints are verified
after each row has been updated, resulting in a constraint error. I also tried
these with no success :

BEGIN TRANSACTION;
UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
COMMIT TRANSACTION;

and

UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM tbUpdateUnique
WHERE b >= 2 ORDER BY b DESC);

This is the content of the NIST test suite file dml027.sql :

START--
-- MODULE DML027

-- SQL Test Suite, V6.0, Interactive SQL, dml027.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION HU

   SELECT USER FROM HU.ECCO;
-- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print

-- TEST:0124 UPDATE UNIQUE column (key = key + 1) interim conflict!

-- setup
 UPDATE UPUNIQ
  SET NUMKEY = NUMKEY + 1;
-- PASS:0124 If 6 rows updated?

  SELECT COUNT(*),SUM(NUMKEY)
   FROM UPUNIQ;
-- PASS:0124 If count = 6 and SUM(NUMKEY) = 30?

-- restore
 ROLLBACK WORK;

-- END TEST >>> 0124 <<< END TEST
-- 

-- TEST:0125 UPDATE UNIQUE column (key = key + 1) no interim conflit!

-- setup
 UPDATE UPUNIQ
  SET NUMKEY = NUMKEY + 1
  WHERE NUMKEY >= 4;
-- PASS:0125 If 3 rows are updated?

  SELECT COUNT(*),SUM(NUMKEY)
   FROM UPUNIQ;
-- PASS:0125 If count = 6 and SUM(NUMKEY) = 27?

-- restore
 ROLLBACK WORK;

-- END TEST >>> 0125 <<< END TEST
-- *END-OF-MODULE
END--

I would like to know if this will be corrected or if it's too time
consuming to even bother. I would like to keep my column unique but I can
manage without if I need to.

Best regards,

Marc-Andre Gosselin
[EMAIL PROTECTED]