On 14/05/2011, at 5:15 AM, Trevor Borgmeier wrote: > I have a database where a field's value is a comma separated list > indicating the basic categories the item belongs to. > > so if the "categories" field has a value of "1,8,15" > > I want to do a query like this: > > SELECT categories FROM myTable WHERE "8" IN (categories); > > but it only finds records where "8" is the only category...
The "in" operator deals with sets (eg explicit list of items or the results of a select), not text strings. You would use "in" like this: select * from MyTable where Category in (1, 8, 15) or: select * from MyTable where Category in (select Category from OtherTable where OtherTable.Name = MyTableName) See: http://www.sqlite.org/lang_expr.html under the heading "The IN and NOT IN operators" > Is there anyway for it to evaluate the contents fo the categories field first > rather than compare it as a whole? There is no function built into SQLite to convert a text string into a set (eg convert "1,8,15" into (1, 8, 15)), but such a function is not needed in this case. You need a better design of your database. SQLite is relational and you need to make your schema relational. > The describe query works in MySQL, but the port doesn't... So far the hack is > to do something like this... > > SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%"; > > but I'm guessing LIKE isn't as efficient, and the query is more awkward. Yes, that will work but yes, it is inefficient. Again, it doesn't use the relational engine that you have at your disposal. > Any advise would be appreciated. Thanks! You need to "normalize" your data structure. One of the demands of a normalized structure is that each column contains only one value. So instead of having multiple Category values stored in the Categories column, you need a separate table that lists each of the Categories linked to its MyTable row. This might look something like this: create table MyTable ( ID integer primary key not null , Name text ) ; create table Category ( ID integer primary key not null , Name text ) ; create table "MyTable Category" ( ID integer primary key not null , MyTable integer not null references MyTable (ID) , Category integer not null references Category (ID) ) ; Once it has some data, you could query like this: select Name from MyTable join "MyTable Category" on MyTable.ID = "MyTable Category".MyTable where "MyTable Category".Category = 8 ; If you're confused, please post your schema, including at least some data, and I'll show you how it works in your case. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases [email protected] -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
