469,315 Members | 1,603 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

A search withina comma seperated list (Is this possible)

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
Jul 23 '05 #1
11 2316
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
Jul 23 '05 #2
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

Jul 23 '05 #3
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
Jul 23 '05 #4
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
Jul 23 '05 #5
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

Jul 23 '05 #6
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.
Jul 23 '05 #7
"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
Jul 23 '05 #8
"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
Jul 23 '05 #9
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.

Jul 23 '05 #10
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

Jul 23 '05 #11
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
Jul 23 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Craig Keightley | last post: by
2 posts views Thread by Pmb | last post: by
3 posts views Thread by Gary Smith | last post: by
6 posts views Thread by orajit | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.