Thanks for the responses. The three table approach you describe is what I normally use, but the export is coming from a filemaker database where they are stored this way so I thought I'd play with it as is. I was surprised when I tried a similar query with the same data in MySQL and saw that it worked -- MySQL parsed the fields contents as if they were separate items. Regardless, I think I'll write a quick script to normalize that data into its own table.
Thanks! on 5/13/11 7:16 PM BareFeetWare wrote: > 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 > develo...@barefeetware.com > -- > Comparison of SQLite GUI tools: > http://www.barefeetware.com/sqlite/compare/?ml > > _______________________________________________ > 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
