How to do I get an 'AND' condition to work in an SQLite query?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to do I get an 'AND' condition to work in an SQLite query?

Scott
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to do I get an 'AND' condition to work in an SQLite query?

Simon Slavin-3
On 31 Jan 2019, at 5:46pm, Scott <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to do I get an 'AND' condition to work in an SQLite query?

Scott
In reply to this post by Scott
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to do I get an 'AND' condition to work in an SQLite query?

Simon Slavin-3
On 31 Jan 2019, at 6:21pm, Scott <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users