Jump to content

one to many recordset without repeating the 'one'


spiderwell

Recommended Posts

Hi all, I am having trouble returning a recordset in the way i want.

I have 2 tables, one is users, the other is user_groups

any user can have 1 or more entrys in the user_groups table, hence the one to many relationship title.

I am trying to return a recordset that only returns 1 row per user with an alias that has a comma seperated list of the groups that the user is in.

In this particular instance there is an extra table joined to the user_groups, called groups which will give names to the int entrys in the users_groups.

 

this is the query i have come up with but it seems to fall over near the XML path bit, but i am not sure how to fix it, my sql is rather weak sadly:

 

SELECT *, Stuff((SELECT ','+ `name` FROM users_groups join groups on users.id = users_groups.user_id WHERE users.id = users_groups.user_id FOR XML Path('')),1,1,'') as `roles` from users limit 10 ;

 

my table structures are:

 

CREATE TABLE `users` (
`id` MEDIUMINT( UNSIGNED NOT NULL AUTO_INCREMENT,
`ip_address` INT(10) UNSIGNED NOT NULL,
`username` VARCHAR(100) NOT NULL,
`password` VARCHAR(40) NOT NULL,
`salt` VARCHAR(40) NULL DEFAULT NULL,
`email` VARCHAR(100) NOT NULL,
`activation_code` VARCHAR(40) NULL DEFAULT NULL,
`forgotten_password_code` VARCHAR(40) NULL DEFAULT NULL,
`forgotten_password_time` INT(11) UNSIGNED NULL DEFAULT NULL,
`remember_code` VARCHAR(40) NULL DEFAULT NULL,
`created_on` INT(11) UNSIGNED NOT NULL,
`last_login` INT(11) UNSIGNED NULL DEFAULT NULL,
`active` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
`first_name` VARCHAR(50) NULL DEFAULT NULL,
`last_name` VARCHAR(50) NULL DEFAULT NULL,
`company` VARCHAR(100) NULL DEFAULT NULL,
`phone` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;

CREATE TABLE `users_groups` (
`id` MEDIUMINT( UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` MEDIUMINT( UNSIGNED NOT NULL,
`group_id` MEDIUMINT( UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3;

CREATE TABLE `groups` (
`id` MEDIUMINT( UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`description` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3;

 

 

 

an assistance would be greatly appreciated!

 

Link to comment
Share on other sites

ok so now i have this:

 

SELECT *, GROUP_CONCAT(groups.name) as `roles` from users join users_groups on users.id = users_groups.user_id join groups on users.id = users_groups.user_id

 

which works but i am getting the 'roles' all listed twice, do you know why that might be?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.