Thanks Dan for the reply, How would I go about creating a sql dump?
As for the ORDER BY clause, I do this: SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC; Although a.some_other_value is not displayed in the tableA below, I forgot to add it. So, I add the ORDER BY clause on a.some_other_value and it returns a ton of records (mostly duplicates). I am pretty sure this is a bug because if I wrap this query inside of a subquery and perform the ORDER BY on the subquery, everything works. This is what WORKS: SELECT * FROM ( SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id = d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC; So for now, I am just leaving my original query inside the subquery to fix it. However, I wouldn't mind posting some debugging information if it will help fix the bug. Thanks, J On Tue, Mar 3, 2009 at 8:23 PM, Dan <danielk1...@gmail.com> wrote: > > On Mar 4, 2009, at 4:41 AM, Jonathon wrote: > > > Hello, > > > > I am executing a query such as: > > Can you post a database (or sql dump thereof) to run your queries > against? Also say exactly what ORDER BY clause you are adding to > the end of the query that causes it to return incorrect results? > > Thanks, > Dan. > > > > > 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c > > INNER > > JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id > > AND > > a.d_id = d.id AND c.e_id = e.id; > > 2. > > 3. CREATE TABLE tableA ( > > 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > 5. b_id INTEGER NOT NULL, > > 6. c_id INTEGER NOT NULL, > > 7. d_id INTEGER NOT NULL > > 8. ) > > 9. > > 10. CREATE TABLE tableB ( > > 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 12. ); > > 13. > > 14. CREATE TABLE tableC ( > > 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > 16. e_id INTEGER NOT NULL > > 17. ); > > 18. > > 19. CREATE TABLE tableD ( > > 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 21. ); > > 22. > > 23. CREATE TABLE tableE ( > > 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT > > 25. ); > > > > > > This seems to be returning the correct records. However, when I > > append an > > ORDER BY at the end of the query, it seems to be changing the number > > of > > records that are returned back to me. From my understanding, an > > ORDER BY > > can not change the number of records correct? If I do not use the > > ORDER BY, > > I get around 150 records. If I do an ORDER BY on any column that > > is not in > > tableA, the number of records blows up (~4k) and there are duplicates. > > > > Any ideas? > > > > I thought that it was because of a buggy parser, so I wrote the > > query again > > as: > > > > > > 1. SELECT * FROM tableA a > > 2. INNER JOIN tableB b > > 3. ON a.b_id = b.id > > 4. INNER JOIN tableD d > > 5. ON a.d_id = d.id > > 6. INNER JOIN tableC c > > 7. LEFT JOIN tableE e ON c.e_id = e.id > > 8. ON ON a.c_id = c.id > > > > and it still gave me the same results... > > > > Thanks, > > J > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
