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
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
[sqlite] SQLite search using IN clause
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] IN clause in search query to search a single field containing comma delimited values
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
