> First of all attachments are stripped out from this list, so nobody > saw your database.
Sorry for that. Now I created a shared folder so everyone has access and can take a look at it: https://docs.google.com/folder/d/0B7kiuyPBHpjqYm8wZmdNcGI3c1E/edit?usp=sharing It contains the database and the full schema as sql script and as a png image for easier overview. > And second your above results can be not definitive if different > datatypes and affinities come into play. So to check things out you > should add la2.id to the above SELECT field list. If it is NULL then > results of query with inner join are correct. I tried your suggestion, but it gave the result I expected (i.e. correct behavior): adding "la2.id" to the column list returned the row "47, 47", so nothing is NULL (therefore I think they should be "innerjoinable"). > And to check why they > are not what you expect you can do the following queries: > > SELECT id, typeof(id) FROM labels WHERE id = 47; > SELECT labelid, typeof(labelid) FROM interval2label WHERE labelid = 47; The first query returns the row "47, integer" (as expected), the second query returns the same row multiple times (as expected). ("labels" and "entryintervals" are in many-to-many connection through "interval2label".) So these debug queries return proper results, but the original problem still exists. > And you could show us schema of these two tables so that we could > explain the results to you. Here is the schema for the two requested tables (and you can find the whole schema in the shared folder): CREATE TABLE interval2label (id INTEGER PRIMARY KEY AUTOINCREMENT, labelid INTEGER, entry_intervalid INTEGER, FOREIGN KEY(labelid) REFERENCES labels(id), FOREIGN KEY(entry_intervalid) REFERENCES entryintervals(id), UNIQUE(entry_intervalid, labelid)); CREATE TABLE labels (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, groupid INTEGER, UNIQUE(name, groupid) FOREIGN KEY(groupid) REFERENCES labelgroups(id)); Thanks for all your hints so far! Hope my answer will help finding the cause... Tamás _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
