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]

Craig Keightley wrote:

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?
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.

I know of the IN syntax but this only works using SELECT * from tblGoods
WHERE gID in (1,2)
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.

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

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.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID='2';

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

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID IN(4,5);

//Aho

[Craig Keightley]

SELECT * FROM yourtable WHERE goodsRefs LIKE '%2%';
I've tried that beofe and the list could have in excess of 500
You usually build 3 tables
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" <us**@example.net> wrote in message
news:39*************@individual.net... Craig Keightley wrote:

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?

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.

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

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.

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

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.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID='2';

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

SELECT tblSuppliers.sName FROM tblSuppliers WHERE tblSuppliers.sID =
tblRefs.sID AND tblRefs.sID IN(4,5);

//Aho

[Oli Filth]

Craig Keightley wrote:

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

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

tblSupplierGoods: 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 tblSupplierGoods for each individual goods ID
that they supply.

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

SELECT tblSuppliers.* FROM
tblSuppliers, tblSupplierGoods, tblGoods
WHERE
tblSupplierGoods.sID = tblSuppliers.sID
AND tblSupplierGoods.gID = tblGoods.gID
AND tblGoods.gName = 'bread'
--
Oli

[J.O. Aho]

Craig Keightley wrote:

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

I've tried that beofe and the list could have in excess of 500

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.

You usually build 3 tables

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

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]

I'll give it a try

thanks
"J.O. Aho" <us**@example.net> wrote in message
news:39*************@individual.net...

Craig Keightley wrote:

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

I've tried that beofe and the list could have in excess of 500

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.

You usually build 3 tables

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

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

[Bill Karwin]

J.O. Aho wrote:

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.

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]

"Craig Keightley" <do**@spam.me> wrote in message
news:42**********************@news-text.dial.pipex.com...

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

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

-Joe

[jerry gitomer]

"Craig Keightley" <do**@spam.me> wrote in message

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

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]

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" <bi**@karwin.com> wrote in message
news:d1*********@enews2.newsguy.com...

J.O. Aho wrote:

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.

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.

[Craig Keightley]

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" <jg******@verizon.net> wrote in message
news:xcWZd.6994$I16.5073@trndny03...

"Craig Keightley" <do**@spam.me> wrote in message

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

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

[jerry gitomer]

Craig Keightley wrote:

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" <jg******@verizon.net> wrote in message
news:xcWZd.6994$I16.5073@trndny03...

"Craig Keightley" <do**@spam.me> wrote in message

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

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

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