The column was originally populated with both numeric and NULL values. I did not specify what type of data would be in the column, preferring to let SQLite dynamically type it. However in this case it has come back to bite me since it appears that any column with both numeric and NULL values will be considered as TEXT.
Thanks taking a look at my SQL syntax. Such a silly problem that I've caused. Thanks again! -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Tuesday, September 08, 2009 9:26 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Not able to properly inner join? On Tue, Sep 8, 2009 at 11:20 AM, Aaron Drake<aar...@bsquare.com> wrote: > The tokens were placeholders all fields are numeric. > > The query SELECT * FROM AlertErrors WHERE id = 10 and code = 50 returns > two rows. The query SELECT * FROM AlertErrors WHERE id = 10 and code = > 50 and subCode = 0 returns zero rows. and therein lies your answer. You JOIN condition is returning no rows, so no AlertErrors.error is being returned to JOIN with ErrorsText.error. SQLite is performing correctly. > > However if I do SELECT * from alerterrors where id = 10 and code = 50 > and subcode = '0' I get two rows. How can it be that subcode is a > character? Dunno. Did you make is a character? SQLite does only what it is asked to do (although sometimes it can be counterintuitive, but it is almost always the operator's error). > Can I recreate the table or alter the column so that it is a > numeric value? > check your table definition. If subcode is not defined as INTEGER, you can create a new table with the proper definitions and then populate the table correctly. > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor > Sent: Tuesday, September 08, 2009 8:46 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Not able to properly inner join? > > On Tue, Sep 8, 2009 at 10:41 AM, Aaron Drake<aar...@bsquare.com> wrote: >> Greetings, >> >> I'm new to SQL and SQLite3, I usually create my SQL queries using a >> query builder and then run it against the SQLite3 engine and tweak it >> until I get my desired result. >> >> I'm trying to join two tables using this statement: >> >> SELECT ErrorsText.*, AlertErrors.extra FROM AlertErrors INNER JOIN >> ErrorsText ON AlertErrors.error = ErrorsText.error WHERE > (AlertErrors.id >> = A) AND (AlertErrors.code = Foo) AND (AlertErrors.subCode = 0) >> > > Assuming that 'A' and 'Foo' above are placeholders and neither actual > strings nor column names, the query looks mostly fine. > > What does the following return? > > SELECT * FROM AlertErrors WHERE id = A and code = Foo and subCode = 0 > > Of course, if A and Foo are actual strings, then you have to enclose > them in single quotes. > >> >> Two rows of data should be returned, I've tried this on other SQL >> engines like SQL Server Compact and it returns both rows as expected. > If >> I remove the last AND clause the rows are returned as expected, but I >> must have it because there are times that a subCode may be crucial in >> retrieving the proper text. >> >> I thought that INNER JOIN was completely working in SQLite3, I don't > see >> it mentioned in the docs as one of the joins that is currently being >> worked on. >> >> Is there some kind of error in my SQL statement? Does SQLite3 parse > SQL >> statements different than...say, a Microsoft product would? What query >> designer do most of you use to create complex queries? Which do you >> recommend for someone like me? >> >> Thanks! >> _______________________________________________ >> 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
