one to many recordset without repeating the 'one'

[spiderwell]

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!

 

[premiso]

This is actually very easy to accomplish, look into the group_concat function:  http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/

[spiderwell]

having a look now, thanks for the link

[spiderwell]

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?

[premiso]

This part is not right. You should be joining groups on users_groups.id = groups.id:

 

join groups on users_groups.group_id = groups.id

[spiderwell]

ah perfect, thanks a lot for your help, my sql definately needs some big improvement!

[spiderwell]

ok so further investigation shows that this isnt working after all, if i have 2 users, it only shows 1 user, and puts both users' roles into 1 user, the first one. I am really not sure where to go next

[Barand]

do you have "GROUP BY user.id"?

 

And lose the SELECT *, specify the columns you need in the SELECT clause

[spiderwell]

ah i hadnt used the group by bit. that has fixed it, thanks again