Use single quotes, not double quotes, for strings. Double quotes are for identifiers. And you have a column named white...
On Fri, Mar 22, 2019, 8:11 AM Steve Horvath <stevejudosen...@gmail.com> wrote: > Hi, > > I found an issue with SQLite 3.27.2 and is also existent in 3.20.1. > > Of the four queries below, the second query should have returned the same > result set as the first query. As a side note, I also tried creating the > tables with no primary keys and got the same results. I also tried using > the "ON" syntax and got the same results. > > sqlite3.exe bugtest > SQLite version 3.27.2 2019-02-25 16:06:06 > Enter ".help" for usage hints. > sqlite> create table competitors (compid INTEGER PRIMARY KEY, last TEXT, > first TEXT); > sqlite> create table matches (category INTEGER, number INTEGER, blue > INTEGER, white INTEGER, PRIMARY KEY(category,number)); > sqlite> insert into matches values(10032,2,1109,1197); > sqlite> insert into competitors values (1109,"SERHAN", "James"); > sqlite> insert into competitors values (1197,"WHITE","Justin"); > sqlite> .headers on > sqlite> select * from matches; > category|number|blue|white > 10032|2|1109|1197 > sqlite> select * from competitors; > compid|last|first > 1109|SERHAN|James > 1197|WHITE|Justin > > sqlite> select competitors.* from matches INNER JOIN competitors WHERE > matches.white = competitors.compid and matches.category=10032 and > matches.number=2 and competitors.first="Justin"; > compid|last|first > 1197|WHITE|Justin > > sqlite> select competitors.* from matches INNER JOIN competitors > WHERE matches.white = competitors.compid and matches.category=10032 and > matches.number=2 and competitors.last="WHITE"; > > sqlite> select competitors.* from matches INNER JOIN competitors > WHERE matches.blue = competitors.compid and matches.category=10032 and > matches.number=2 and competitors.first="James"; > compid|last|first > 1109|SERHAN|James > > sqlite> select competitors.* from matches INNER JOIN competitors WHERE > matches.blue = competitors.compid and matches.category=10032 and > matches.number=2 and competitors.last="SERHAN"; > compid|last|first > 1109|SERHAN|James > > sqlite>.quit > > Out of curiosity, I changed the last name of "WHITE" to "WHTE" and queries > now work!?!? > > sqlite3.exe bugtest > SQLite version 3.27.2 2019-02-25 16:06:06 > Enter ".help" for usage hints. > sqlite> update competitors set last="WHTE" where compid=1197; > sqlite> select competitors.* from matches INNER JOIN competitors WHERE > matches.white = competitors.compid and matches.category=10032 and > matches.number=2 and competitors.last="WHTE"; > 1197|WHTE|Justin > sqlite> select competitors.* from matches INNER JOIN competitors WHERE > matches.white = competitors.compid and matches.category=10032 and > matches.number=2 and competitors.first="Justin"; > 1197|WHTE|Justin > sqlite> select competitors.* from matches INNER JOIN competitors ON > matches.blue = competitors.compid where matches.category=10032 and > matches.number=2 and competitors.first="James"; > 1109|SERHAN|James > sqlite> select competitors.* from matches INNER JOIN competitors ON > matches.blue = competitors.compid where matches.category=10032 and > matches.number=2 and competitors.last="SERHAN"; > 1109|SERHAN|James > sqlite> .quit > > I hope you have enough details to troubleshoot! > > Thanks in advance, > STeve Horvath > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
