A search withina comma seperated list (Is this possible)

[Craig Keightley]

I have a mysql database with a list of companies who supply specific
products

tblSuppliers (simplified)

sID | sName | goodsRefs
1 | comp name | 1,2,3,4,5
2 | company 2 | 2,4


tblGoods (simplified)

gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate


The idea is that the comma seperated list relates to the goods they supply

is it possible to show all suppliers who provide bread?

I know of the IN syntax but this only works using SELECT * from tblGoods
WHERE gID in (1,2)

How do i create a SELECT Statement to show the companies containing the ID
2 in their respective comma separted list

Any help would be gratefully appreciated

Craig

[J.O. Aho]

Re: A search withina comma seperated list (Is this possible)

Craig Keightley wrote:[color=blue]
> I have a mysql database with a list of companies who supply specific
> products
>
> tblSuppliers (simplified)
>
> sID | sName | goodsRefs
> 1 | comp name | 1,2,3,4,5
> 2 | company 2 | 2,4
>
>
> tblGoods (simplified)
>
> gID | gName
> 1 | fish
> 2 | bread
> 3 | apples
> 4 | bananas
> 5 | chocolate
>
>
> The idea is that the comma seperated list relates to the goods they supply
>
> is it possible to show all suppliers who provide bread?[/color]

SELECT * FROM yourtable WHERE goodsRefs LIKE '%2%';

If you have 20 or more goods, then the query would generate false results, but
then you can build a long and complicated query with a handfull OR.

[color=blue]
> I know of the IN syntax but this only works using SELECT * from tblGoods
> WHERE gID in (1,2)[/color]

IN() does take a handfull arguments and compares them to the column, the
values has to be of same type, as your goodsRefs is varchar, you can't compare
it to int.

[color=blue]
> How do i create a SELECT Statement to show the companies containing the ID
> 2 in their respective comma separted list[/color]

You usually build 3 tables

tblSuppliers (simplified)
sID | sName
1 | comp name
2 | company 2

tblGoods (simplified)
gID | gName
1 | fish
2 | bread
3 | apples
4 | bananas
5 | chocolate

tblRefs
sID | gID
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
2 | 2
2 | 4

You then join the tables when you want something out of them

http://dev.mysql.com/doc/mysql/en/join.html

SELECT tblSuppliers.sN ame FROM tblSuppliers WHERE tblSuppliers.sI D =
tblRefs.sID AND tblRefs.sID='2' ;

or if you search for more than one, say chocolate and bananas

SELECT tblSuppliers.sN ame FROM tblSuppliers WHERE tblSuppliers.sI D =
tblRefs.sID AND tblRefs.sID IN(4,5);



//Aho

[Craig Keightley]

Re: A search withina comma seperated list (Is this possible)

[color=blue]
> SELECT * FROM yourtable WHERE goodsRefs LIKE '%2%';[/color]

I've tried that beofe and the list could have in excess of 500
[color=blue]
> You usually build 3 tables[/color]

isn't that long winded?
The suppliers will be added via a web interface, where they can add a
supplier and choose what products they provide, the 3rd able may not work



"J.O. Aho" <user@example.n et> wrote in message
news:39oc78F63j rusU1@individua l.net...[color=blue]
> Craig Keightley wrote:[color=green]
>> I have a mysql database with a list of companies who supply specific
>> products
>>
>> tblSuppliers (simplified)
>>
>> sID | sName | goodsRefs
>> 1 | comp name | 1,2,3,4,5
>> 2 | company 2 | 2,4
>>
>>
>> tblGoods (simplified)
>>
>> gID | gName
>> 1 | fish
>> 2 | bread
>> 3 | apples
>> 4 | bananas
>> 5 | chocolate
>>
>>
>> The idea is that the comma seperated list relates to the goods they
>> supply
>>
>> is it possible to show all suppliers who provide bread?[/color]
>
> SELECT * FROM yourtable WHERE goodsRefs LIKE '%2%';
>
> If you have 20 or more goods, then the query would generate false results,
> but then you can build a long and complicated query with a handfull OR.
>
>[color=green]
>> I know of the IN syntax but this only works using SELECT * from tblGoods
>> WHERE gID in (1,2)[/color]
>
> IN() does take a handfull arguments and compares them to the column, the
> values has to be of same type, as your goodsRefs is varchar, you can't
> compare it to int.
>
>[color=green]
>> How do i create a SELECT Statement to show the companies containing the
>> ID 2 in their respective comma separted list[/color]
>
> You usually build 3 tables
>
> tblSuppliers (simplified)
> sID | sName
> 1 | comp name
> 2 | company 2
>
> tblGoods (simplified)
> gID | gName
> 1 | fish
> 2 | bread
> 3 | apples
> 4 | bananas
> 5 | chocolate
>
> tblRefs
> sID | gID
> 1 | 1
> 1 | 2
> 1 | 3
> 1 | 4
> 1 | 5
> 2 | 2
> 2 | 4
>
> You then join the tables when you want something out of them
> http://dev.mysql.com/doc/mysql/en/join.html
>
> SELECT tblSuppliers.sN ame FROM tblSuppliers WHERE tblSuppliers.sI D =
> tblRefs.sID AND tblRefs.sID='2' ;
>
> or if you search for more than one, say chocolate and bananas
>
> SELECT tblSuppliers.sN ame FROM tblSuppliers WHERE tblSuppliers.sI D =
> tblRefs.sID AND tblRefs.sID IN(4,5);
>
>
>
> //Aho[/color]

[Oli Filth]

Re: A search withina comma seperated list (Is this possible)

Craig Keightley wrote:[color=blue]
> I have a mysql database with a list of companies who supply specific
> products
>
> tblSuppliers (simplified)
>
> sID | sName | goodsRefs
> 1 | comp name | 1,2,3,4,5
> 2 | company 2 | 2,4
>
>[/color]

Having multiple pieces of data in the same field in a table is a BAD
idea, for reasons that you are discovering. Have each entry on a
separate line, or even better (to avoid duplication of sName), arrange
your db as so:

tblSuppliers: sID, sName

tblSupplierGood s: sID, gID

tblGoods: gID, gName

i.e. each company has ONE entry in tblSuppliers linking their name to an
ID, and then an entry in tblSupplierGood s for each individual goods ID
that they supply.

To find which manufacturers supply bread, your query would be:

SELECT tblSuppliers.* FROM
tblSuppliers, tblSupplierGood s, tblGoods
WHERE
tblSupplierGood s.sID = tblSuppliers.sI D
AND tblSupplierGood s.gID = tblGoods.gID
AND tblGoods.gName = 'bread'


--
Oli

[J.O. Aho]

Re: A search withina comma seperated list (Is this possible)

Craig Keightley wrote:[color=blue][color=green]
>>SELECT * FROM yourtable WHERE goodsRefs LIKE '%2%';[/color]
>
> I've tried that beofe and the list could have in excess of 500[/color]


SELECT * FROM yourtable WHERE (goodsRefs LIKE '%,2,%') OR (goodsRefs LIKE
'2,%') OR (goodsRefs LIKE '%,2') OR (goodsRefs LIKE '2');

That should cover all the possibilities for the 2, to be in the middle, be
first, be last or be the only one.

[color=blue][color=green]
>>You usually build 3 tables[/color]
>
> isn't that long winded?
> The suppliers will be added via a web interface, where they can add a
> supplier and choose what products they provide, the 3rd able may not work[/color]

You normaly come to this at the second normal form if I remeber it right
(otherwise it's on the third). It will be a lot easier for you to code your
system and a lot easier to search too and this is what is the point in using a
relation database, otherwise you could just have everything in one textfile
with all data about one company in one long row.


//Aho

[Craig Keightley]

Re: A search withina comma seperated list (Is this possible)

I'll give it a try

thanks
"J.O. Aho" <user@example.n et> wrote in message
news:39oedkF64m jt4U1@individua l.net...[color=blue]
> Craig Keightley wrote:[color=green][color=darkred]
>>>SELECT * FROM yourtable WHERE goodsRefs LIKE '%2%';[/color]
>>
>> I've tried that beofe and the list could have in excess of 500[/color]
>
>
> SELECT * FROM yourtable WHERE (goodsRefs LIKE '%,2,%') OR (goodsRefs LIKE
> '2,%') OR (goodsRefs LIKE '%,2') OR (goodsRefs LIKE '2');
>
> That should cover all the possibilities for the 2, to be in the middle, be
> first, be last or be the only one.
>
>[color=green][color=darkred]
>>>You usually build 3 tables[/color]
>>
>> isn't that long winded?
>> The suppliers will be added via a web interface, where they can add a
>> supplier and choose what products they provide, the 3rd able may not work[/color]
>
> You normaly come to this at the second normal form if I remeber it right
> (otherwise it's on the third). It will be a lot easier for you to code
> your system and a lot easier to search too and this is what is the point
> in using a relation database, otherwise you could just have everything in
> one textfile with all data about one company in one long row.
>
>
> //Aho[/color]

[Bill Karwin]

Re: A search withina comma seperated list (Is this possible)

J.O. Aho wrote:[color=blue]
> SELECT * FROM yourtable WHERE (goodsRefs LIKE '%,2,%') OR (goodsRefs
> LIKE '2,%') OR (goodsRefs LIKE '%,2') OR (goodsRefs LIKE '2');
>
> That should cover all the possibilities for the 2, to be in the middle,
> be first, be last or be the only one.[/color]

You should be able to do this with regular expression matching too.
MySQL regular expressions include a "word boundary" marker:

SELECT * FROM tblSuppliers
WHERE goodsRefs REGEXP '[[:<:]]2[[:>:]]';

You also could do a join so you could get the gName from the tblGoods
table too.

SELECT s.*, g.gName
FROM tblSuppliers AS s INNER JOIN tblGoods AS g
ON s.goodsRef REGEXP CONCAT('[[:<:]]', g.gID, '[[:>:]]');

Regards,
Bill K.

[Joe Webster]

Re: A search withina comma seperated list (Is this possible)

"Craig Keightley" <dont@spam.me > wrote in message
news:4236f686$0 $2764$cc9e4d1f@ news-text.dial.pipex .com...[color=blue]
> I have a mysql database with a list of companies who supply specific
> products
>
> tblSuppliers (simplified)
>
> sID | sName | goodsRefs
> 1 | comp name | 1,2,3,4,5
> 2 | company 2 | 2,4
>
>
> tblGoods (simplified)
>
> gID | gName
> 1 | fish
> 2 | bread
> 3 | apples
> 4 | bananas
> 5 | chocolate
>
>
> The idea is that the comma seperated list relates to the goods they supply
>
> is it possible to show all suppliers who provide bread?
>
> I know of the IN syntax but this only works using SELECT * from tblGoods
> WHERE gID in (1,2)
>
> How do i create a SELECT Statement to show the companies containing the[/color]
ID[color=blue]
> 2 in their respective comma separted list
>
> Any help would be gratefully appreciated
>
> Craig
>[/color]

Use the mysql function FIND_IN_SET. I believe the IN function works
similarly or may even be an alias.

-Joe

[jerry gitomer]

Re: A search withina comma seperated list (Is this possible)

"Craig Keightley" <dont@spam.me > wrote in message[color=blue]
>
>I have a mysql database with a list of companies who supply specific
>products
>
>tblSuppliers (simplified)
>
>sID | sName | goodsRefs
>1 | comp name | 1,2,3,4,5
>2 | company 2 | 2,4
>
>
>tblGoods (simplified)
>
>gID | gName
>1 | fish
>2 | bread
>3 | apples
>4 | bananas
>5 | chocolate
>
>
>The idea is that the comma seperated list relates to the goods they supply
>
>is it possible to show all suppliers who provide bread?
>
>I know of the IN syntax but this only works using SELECT * from tblGoods
>WHERE gID in (1,2)
>
>How do i create a SELECT Statement to show the companies containing the
>2 in their respective comma separted list
>
>Any help would be gratefully appreciated
>
>Craig
>[/color]
Craig,
What you want to do is probably not a good idea. You have a
denormalized data structure with an arbitrary length embedded
array.

You can simplify your code, simplify future maintenance and
increase your performance by breaking the array out into an
attribute:value table indexed on both attributes and values.
Your table would take the form sID:gID and would look like:

ABC fish
ABC bread
ABC apples
XYZ fish

(Instead of using the names of the companies and goods use the
index values for the tables that contain them.)

Using this approach you can get the information you require
using simple joins and let the RDBMS do the work for you.

HTH

Jerry

[Craig Keightley]

Re: A search withina comma seperated list (Is this possible)

The SQL :

SELECT * FROM yourtable WHERE (goodsRefs LIKE '%,2,%') OR (goodsRefs
LIKE '2,%') OR (goodsRefs LIKE '%,2') OR (goodsRefs LIKE '2');

works just as i needed, thanks for that

Craig



"Bill Karwin" <bill@karwin.co m> wrote in message
news:d179q501jb o@enews2.newsgu y.com...[color=blue]
> J.O. Aho wrote:[color=green]
>> SELECT * FROM yourtable WHERE (goodsRefs LIKE '%,2,%') OR (goodsRefs LIKE
>> '2,%') OR (goodsRefs LIKE '%,2') OR (goodsRefs LIKE '2');
>>
>> That should cover all the possibilities for the 2, to be in the middle,
>> be first, be last or be the only one.[/color]
>
> You should be able to do this with regular expression matching too. MySQL
> regular expressions include a "word boundary" marker:
>
> SELECT * FROM tblSuppliers
> WHERE goodsRefs REGEXP '[[:<:]]2[[:>:]]';
>
> You also could do a join so you could get the gName from the tblGoods
> table too.
>
> SELECT s.*, g.gName
> FROM tblSuppliers AS s INNER JOIN tblGoods AS g
> ON s.goodsRef REGEXP CONCAT('[[:<:]]', g.gID, '[[:>:]]');
>
> Regards,
> Bill K.[/color]

[Craig Keightley]

Re: A search withina comma seperated list (Is this possible)

is it possible to compare acomma separated list aginst another

eg comma list 1 => 1,2,3,4,5
comma list 2 => 3,5,6,9

can you check that 3 is in both, and 5 is in both, therfore they match???


"jerry gitomer" <jgitomer@veriz on.net> wrote in message
news:xcWZd.6994 $I16.5073@trndn y03...[color=blue]
> "Craig Keightley" <dont@spam.me > wrote in message[color=green]
>> I have a mysql database with a list of companies who supply specific
>>products
>>
>>tblSupplier s (simplified)
>>
>>sID | sName | goodsRefs
>>1 | comp name | 1,2,3,4,5
>>2 | company 2 | 2,4
>>
>>
>>tblGoods (simplified)
>>
>>gID | gName
>>1 | fish
>>2 | bread
>>3 | apples
>>4 | bananas
>>5 | chocolate
>>
>>
>>The idea is that the comma seperated list relates to the goods they supply
>>
>>is it possible to show all suppliers who provide bread?
>>
>>I know of the IN syntax but this only works using SELECT * from tblGoods
>>WHERE gID in (1,2)
>>
>>How do i create a SELECT Statement to show the companies containing the
>>2 in their respective comma separted list
>>
>>Any help would be gratefully appreciated
>>
>>Craig
>>[/color]
> Craig,
> What you want to do is probably not a good idea. You have a denormalized
> data structure with an arbitrary length embedded array.
>
> You can simplify your code, simplify future maintenance and increase your
> performance by breaking the array out into an attribute:value table
> indexed on both attributes and values. Your table would take the form
> sID:gID and would look like:
>
> ABC fish
> ABC bread
> ABC apples
> XYZ fish
>
> (Instead of using the names of the companies and goods use the index
> values for the tables that contain them.)
>
> Using this approach you can get the information you require using simple
> joins and let the RDBMS do the work for you.
>
> HTH
>
> Jerry[/color]

[jerry gitomer]

Re: A search withina comma seperated list (Is this possible)

Craig Keightley wrote:[color=blue]
> is it possible to compare acomma separated list aginst another
>
> eg comma list 1 => 1,2,3,4,5
> comma list 2 => 3,5,6,9
>
> can you check that 3 is in both, and 5 is in both, therfore they match???
>
>
> "jerry gitomer" <jgitomer@veriz on.net> wrote in message
> news:xcWZd.6994 $I16.5073@trndn y03...
>[color=green]
>>"Craig Keightley" <dont@spam.me > wrote in message
>>[color=darkred]
>>>I have a mysql database with a list of companies who supply specific
>>>products
>>>
>>>tblSupplie rs (simplified)
>>>
>>>sID | sName | goodsRefs
>>>1 | comp name | 1,2,3,4,5
>>>2 | company 2 | 2,4
>>>
>>>
>>>tblGoods (simplified)
>>>
>>>gID | gName
>>>1 | fish
>>>2 | bread
>>>3 | apples
>>>4 | bananas
>>>5 | chocolate
>>>
>>>
>>>The idea is that the comma seperated list relates to the goods they supply
>>>
>>>is it possible to show all suppliers who provide bread?
>>>
>>>I know of the IN syntax but this only works using SELECT * from tblGoods
>>>WHERE gID in (1,2)
>>>
>>>How do i create a SELECT Statement to show the companies containing the
>>>2 in their respective comma separted list
>>>
>>>Any help would be gratefully appreciated
>>>
>>>Craig
>>>[/color]
>>
>>Craig,
>>What you want to do is probably not a good idea. You have a denormalized
>>data structure with an arbitrary length embedded array.
>>
>>You can simplify your code, simplify future maintenance and increase your
>>performance by breaking the array out into an attribute:value table
>>indexed on both attributes and values. Your table would take the form
>>sID:gID and would look like:
>>
>>ABC fish
>>ABC bread
>>ABC apples
>>XYZ fish
>>
>>(Instead of using the names of the companies and goods use the index
>>values for the tables that contain them.)
>>
>>Using this approach you can get the information you require using simple
>>joins and let the RDBMS do the work for you.
>>
>>HTH
>>
>>Jerry[/color]
>
>
>[/color]

Yes, you can -- but you shouldn't. To find values in your comma
separated list you would do a SELECT ... LIKE ... To work with
the output of two such queries you would use a second SELECT ...
LIKE ... in a sub-query.

HTH

Jerry