Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?
On 31 Jan 2019, at 6:21pm, Scott wrote: > Figured it out! I had set the column Deleted to "CHAR" but all the fields > without 'X' were null. If I replaced null with a valid character it worked. > Thanks for your time. Ah, JOINing on NULL. Well done. For future reference, SQLite doesn't have a CHAR type. For clarity you might want to use TEXT instead. SQLite does assume TEXT when you specify CHAR, but you might be depending on a specifically CHAR behaviour, like truncation to one character, and SQLite will ignore it. Well done for solving your problem. The magic of posting. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?
Figured it out! I had set the column Deleted to "CHAR" but all the fields without 'X' were null. If I replaced null with a valid character it worked. Thanks for your time. Scott ValleryEcclesiastes 4:9-10 On Thursday, January 31, 2019, 12:46:34 PM EST, Scott wrote: I can return results successfully from the t.Topic and n.Deleted columns separately, but when I try to use AND I receive no results. I'm not sure what I may be doing wrong. This is my first exhaustive work with a database project, so I've had to learn some syntax along the way, but has me stumped. SELECT n.NoteID, s.SourceType, s.Title, c.Summary FROM Comment as c LEFT JOIN Notes as n ON n.CommentID = c.CommentID LEFT JOIN Source as s ON n.SourceID = s.SourceID LEFT JOIN Topic as t ON n.TopicID = t.TopicID WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') I've tried and even with INNER JOIN: (WHERE t.Topic = 'Manuscript Copies') AND n.Deleted <> 'X')WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') WHERE t.Topic = 'Manuscript Copies' AND n.Deleted <> 'X' Thanks, Scott ValleryEcclesiastes 4:9-10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?
On 31 Jan 2019, at 5:46pm, Scott wrote: > I can return results successfully from the t.Topic and n.Deleted columns > separately, but when I try to use AND I receive no results. There was an optimization bug that looked like your example in some previous version of SQLite. Are you running an up-to-date version of SQLite ? Are you doing this in your own code or in the SQLite CLI tool ? If possible, please test in the CLI tool. Please execute "PRAGMA schema.integrity_check" just in case your database is corrupt. Try removing everything that doesn't cause the problem. So remove the JOIN to the Source table. Swap the order of the conditions around the "AND". Swap the order of the JOIN clauses. Some combination of the above should tell you more about the problem. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to do I get an 'AND' condition to work in an SQLite query?
I can return results successfully from the t.Topic and n.Deleted columns separately, but when I try to use AND I receive no results. I'm not sure what I may be doing wrong. This is my first exhaustive work with a database project, so I've had to learn some syntax along the way, but has me stumped. SELECT n.NoteID, s.SourceType, s.Title, c.Summary FROM Comment as c LEFT JOIN Notes as n ON n.CommentID = c.CommentID LEFT JOIN Source as s ON n.SourceID = s.SourceID LEFT JOIN Topic as t ON n.TopicID = t.TopicID WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') I've tried and even with INNER JOIN: (WHERE t.Topic = 'Manuscript Copies') AND n.Deleted <> 'X')WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X') WHERE t.Topic = 'Manuscript Copies' AND n.Deleted <> 'X' Thanks, Scott ValleryEcclesiastes 4:9-10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
