StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLite on Android: Different result set at Runtime vs. SQLite Administrator
text
Body
copied!<p>I am building an Android app with an internal SQLite DB. Here is the schema: </p> <pre><code>CREATE TABLE IF NOT EXISTS [tblImageVideoLink] ( [LinkID] INTEGER NOT NULL PRIMARY KEY, [ImageID] INTEGER UNIQUE NOT NULL, [VideoID] INTEGER NULL ); CREATE TABLE IF NOT EXISTS [tblImages] ( [ID] INTEGER PRIMARY KEY NOT NULL, [ImageName] VARCHAR(50) NOT NULL, [ImageDescription] VARCHAR(100) NULL, [Page] INTEGER NULL ); CREATE TABLE IF NOT EXISTS [tblPages] ( [ID] INTEGER NOT NULL PRIMARY KEY, [PageName] VARCHAR(30) NULL ); CREATE TABLE IF NOT EXISTS [tblVideos] ( [ID] INTEGER NOT NULL PRIMARY KEY, [VideoName] VARCHAR(150) NOT NULL, [VideoDescription] VARCHAR(100) NULL, [VideoType] VARCHAR(10) NULL ); CREATE INDEX IF NOT EXISTS [IDX_TBLIMAGEVIDEOLINK_IMAGEID] ON [tblImageVideoLink]( [ImageID] DESC, [VideoID] DESC ); CREATE INDEX IF NOT EXISTS [IDX_TBLIMAGES_PAGE] ON [tblImages]( [Page] DESC ); </code></pre> <p>Here's the relevant data I have in the tables: </p> <pre><code>INSERT INTO tblImages (ID, ImageName, Page) VALUES (1, 'Beach.jpg', 1); INSERT INTO tblImages (ID, ImageName, Page) VALUES (2, 'Bowling.jpg', 1); INSERT INTO tblImages (ID, ImageName, Page) VALUES (3, 'Car.jpg', 1); INSERT INTO tblVideos (ID, VideoName) VALUES (2, 'Bowling.3gp'); INSERT INTO tblVideos (ID, VideoName) VALUES (3, 'Car.3gp'); INSERT INTO tblImageVideoLink (LinkID, ImageID, VideoID) VALUES (1, 2, 2); INSERT INTO tblImageVideoLink (LinkID, ImageID, VideoID) VALUES (2, 3, 3); INSERT INTO tblPages (ID, PageName) VALUES (1, 'Misc'); </code></pre> <p>I am trying to run this query to get all the images with a certain page, and their related videos: </p> <pre><code>SELECT DISTINCT I.ID AS 'Image ID', I.ImageName, V.ID AS 'Video ID', V.VideoName FROM tblImages I LEFT JOIN tblImageVideoLink L ON L.VideoID=V.ID LEFT JOIN tblVideos V ON L.ImageID=I.ID WHERE I.Page=1; </code></pre> <p>When I test it in <a href="http://sqliteadmin.orbmu2k.de/" rel="nofollow noreferrer">SQLite Administrator</a>, I am getting the <strong>desired</strong> result set, which is: <img src="https://i.stack.imgur.com/WgkAo.jpg" alt="enter image description here"></p> <p>When I test it in the App (or in <a href="http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index" rel="nofollow noreferrer">SQLiteSpy</a>) I am getting a <em>different</em> result set: <img src="https://i.stack.imgur.com/GDLxr.jpg" alt="enter image description here"></p> <p>I have tried everything I know, including GROUP BY, removing the DISTINCT, different JOIN types, etc.</p> <p>BTW, SQLiteSpy writes at the bottom: SQLite 3.7.8 while SQLite Administrator writes SQLite 3.5.1. I don't know if it matters.</p> <p>Please help, and also kindly explain why there's a difference between two SQLite tools...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload