By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 1,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

select statement with multiple value where

P: n/a
I'm fairly new to creating complex sql statements and need a little
help.
In plain english I want to do this: Select menu rows where the row's
userGroupIDs equal the user's userGroupIDs.

Menu.userGroupID has multiple values seperated by commas.
User.userGroupID also has multiple values seperated by commas. I
realize some may want me to create seperate tables for these multiple
values. The reason I'm using comma seperation at the moment is because
I used a multi value <select> on a web page to collect and display the
choices from a single table.

Could I get an example of how to do this using a comma seperated field?

Many thanks.

Nov 8 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 08/11/2005, rhamlin wrote:
I'm fairly new to creating complex sql statements and need a little
help.
In plain english I want to do this: Select menu rows where the row's
userGroupIDs equal the user's userGroupIDs.

Menu.userGroupID has multiple values seperated by commas.
User.userGroupID also has multiple values seperated by commas.
Ouch.
I realize some may want me to create seperate tables for these
multiple values.
Not some. Most.
Could I get an example of how to do this using a comma seperated
field?


See what (nonstandard) messy SQL you need to do this:

USE test;
DROP TABLE IF EXISTS users, menus, numbers;
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY,
groupids CHAR(50)
);

INSERT INTO users VALUES
(1, '10,20,50');

CREATE TABLE menus (
id INT NOT NULL PRIMARY KEY,
groupids CHAR(50) NOT NULL
);

INSERT INTO menus VALUES
(1, '10,20'), (2, '10,30'), (3, '100,200'), (4, '50');
DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (
n TINYINT UNSIGNED NOT NULL PRIMARY KEY
);

INSERT INTO numbers VALUES (1), (2), (3), (4), (5);
-- as many as necessary
SELECT DISTINCT
menus.id
FROM users
JOIN numbers ON
numbers.n <= LENGTH(users.groupids) -
LENGTH(REPLACE(users.groupids, ',','')) + 1
JOIN menus ON
FIND_IN_SET(SUBSTRING_INDEX(
SUBSTRING_INDEX(users.groupids, ',', numbers.n),
',', -1), menus.groupids)
WHERE
users.id = 1
ORDER BY menus.id;
--
felix
Nov 8 '05 #2

P: n/a
Thanks for your detailed answer Felix.

This doesn't look like the direction inwhich I want to go. Looks like
you needed another table anyway. I think I'll create userGroup tables
for Menu and Users and see how I do...

Nov 8 '05 #3

P: n/a
On 8 Nov 2005 05:28:10 -0800, in mailing.database.mysql "rhamlin"
<rh*****@eaglebrass.com> wrote:
| I'm fairly new to creating complex sql statements and need a little
| help.
| In plain english I want to do this: Select menu rows where the row's
| userGroupIDs equal the user's userGroupIDs.
|
| Menu.userGroupID has multiple values seperated by commas.
| User.userGroupID also has multiple values seperated by commas. I
| realize some may want me to create seperate tables for these multiple
| values. The reason I'm using comma seperation at the moment is because
| I used a multi value <select> on a web page to collect and display the
| choices from a single table.
|
| Could I get an example of how to do this using a comma seperated field?
|
| Many thanks.

Have a look at find_in_set function. The only drawback is that there
can be no spaces within this list i.e find_in_set(id,'1,2,3') not
find_in_set(id,'1, 2, 3')
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Nov 8 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.