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
