on 5/14/11 9:02 AM Trevor Borgmeier wrote: > ...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.
I just wanted to correct my statement about my MySQL query -- I wasn't using IN, I was using FIND_IN_SET() -- which doesn't appear to exist in SQLite either. Either way, I've already set up a script to import the field contents into another table to do a proper join instead. -Trevor > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
