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
