MySQL Server Forum - select statement with multiple value where

Author

select statement with multiple value where

rhamlin

2005-11-08, 3:57 pm

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.

Felix Geerinckx

2005-11-08, 3:57 pm

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(SUBSTRIN
G_INDEX(
SUBSTRING_INDEX(user
s.groupids, ',', numbers.n),
',', -1), menus.groupids)
WHERE
users.id = 1
ORDER BY menus.id;

--
felix

rhamlin

2005-11-08, 3:57 pm

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

Jeff North

2005-11-08, 3:57 pm

On 8 Nov 2005 05:28:10 -0800, in mailing.database.mysql "rhamlin"
<rhamlin@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')
---------------------------------------------------------------
jnorthau@yourpantsya
hoo.com.au : Remove your pants to reply
---------------------------------------------------------------

Sponsored Links