Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
On 05/15/2011 09:14 AM, Igor Tandetnik wrote: > Pontiac wrote: >> On 05/13/2011 03:15 PM, Trevor Borgmeier wrote: >>> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%"; >> Careful with how you have your like. As an added bonus, if you were to >> hunt for category 1, your statement would be LIKE "%,1,%" which would >> return no results. > Will too. Note what's on the left hand side of LIKE. My bad. Eyes skipped right over that. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
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
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
Pontiac wrote: > On 05/13/2011 03:15 PM, Trevor Borgmeier wrote: >> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%"; > > Careful with how you have your like. As an added bonus, if you were to > hunt for category 1, your statement would be LIKE "%,1,%" which would > return no results. Will too. Note what's on the left hand side of LIKE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
On 05/13/2011 03:15 PM, 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... > > Is there anyway for it to evaluate the contents fo the categories field > first rather than compare it as a whole? > > 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. Any advise would be appreciated. Thanks! > > -Trevor > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Careful with how you have your like. As an added bonus, if you were to hunt for category 1, your statement would be LIKE "%,1,%" which would return no results. If you're importing the data, I'd suggest, as others have, to massage the data and create the additional table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
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
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
On May 13, 2011 8:17 PM, "BareFeetWare" wrote: > 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. I've a table function hack (using virtual takes) that provides two table functions: string splitting (think of the opposite of group_concat()) and counting (including cycling). I am currently focusing on other work, but maybe I should post what I have for table functions somewhere? Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
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
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%"; > > but I'm guessing LIKE isn't as efficient, and the query is more > awkward. Any advise would be appreciated. Thanks! This LIKE is the only way to get information from your table. But you can do it more efficiently by changing your database schema. I'd suggest to have additional table to store all those categories separately, one category per row (with columns like id_from_table, category). Then you'll be able to have a simple and efficient query on it. Pavel On Fri, May 13, 2011 at 3:15 PM, 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... > > Is there anyway for it to evaluate the contents fo the categories field > first rather than compare it as a whole? > > 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. Any advise would be appreciated. Thanks! > > -Trevor > ___ > 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
Re: [sqlite] IN clause in search query to search a single field containing comma delimited values
On May 13, 2011, at 9:15 PM, Trevor Borgmeier wrote: > so if the "categories" field has a value of "1,8,15" Yep, it's a text column. Nothing relational about it. > Any advise would be appreciated. Normalize your design. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users