Re: [sqlite] IN clause in search query to search a single field containing comma delimited values

[Pontiac]

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

[Trevor Borgmeier]

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

[Igor Tandetnik]

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

[Pontiac]

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

[Trevor Borgmeier]

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

[Nico Williams]

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

[BareFeetWare]

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

[Pavel Ivanov]

> 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

[Petite Abeille]

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