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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
