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.
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? Can I recreate the table or alter the column so that it is a numeric value? -----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 > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ 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
