| Author |
 Topic  |
|
|
lildrc
Starting Member
8 Posts |
 Posted - 07/07/2011 : 05:47:24
|
Hi,
I need to get a list of all users and the groups they belong to with a restriction_id of 10 from these tables:
tUser
------
user_id
user_name
user_email
restriction_id
tAssoc
------
user_id
group_id
tGroup
------
group_id
group_name
As you can see a user can belong to more than one group. is it possible to get all of the user data along with comma separated group_name values in a group column in a single query?
Cheers.
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 05:53:52
|
select u.*, g.group_name
from tuser u
join tassoc a
on u.user_id = a.user_id
join tgroup g
on g.group_id = a.group_id
where u.restriction_id = 10
that will give the resultset
The csv string you can get from
http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 05:57:59
|
with cte as
(
select u.*, g.group_name
from tuser u
join tassoc a
on u.user_id = a.user_id
join tgroup g
on g.group_id = a.group_id
where u.restriction_id = 10
)
select t.*,
csvstr = stuff (
(
select ',' + t2.group_name
from cte t2 where t.tuser_id = t2.user_id
for xml path('')
)
,1,1,'')
from (select distinct user_id, user_name, user_email from cte) t
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
lildrc
Starting Member
8 Posts |
Posted - 07/07/2011 : 06:05:57
|
Cheers for your help Nigel!
That last query works but only gets out 423 users. A straight query on users with a restriction_id of 10 yields 428 results.
*edit*
I''m guessing the missing 5 users don't appear in the tAssoc table. Could this query be edited to include these users and also to group by the csvstr?
Thanks again. |
Edited by - lildrc on 07/07/2011 06:10:00 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 06:11:12
|
try this - think it should give null for the csv string for the ones without associations
with cte as
(
select u.*, g.group_name
from tuser u
left join tassoc a
on u.user_id = a.user_id
left join tgroup g
on g.group_id = a.group_id
where u.restriction_id = 10
)
select t.*,
csvstr = stuff (
(
select ',' + t2.group_name
from cte t2 where t.tuser_id = t2.user_id
for xml path('')
)
,1,1,'')
from (select distinct user_id, user_name, user_email from cte) t
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
lildrc
Starting Member
8 Posts |
Posted - 07/07/2011 : 06:18:00
|
Hi,
That only returned one user with a null csvstring unfortunately.
Thanks for your help though. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 06:22:26
|
odd
try
with cte as
(
select u.*, g.group_name
from tuser u
left join tassoc a
on u.user_id = a.user_id
left join tgroup g
on g.group_id = a.group_id
where u.restriction_id = 10
)
select distinct user_id from cte
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
lildrc
Starting Member
8 Posts |
Posted - 07/07/2011 : 06:25:00
|
| That just returned a single user_id for some reason. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 06:29:08
|
Has your data changed or you're only displaying the first row?
Changing the joins to left joins should get more data or the same.
try
with cte as
(
select u.*
from tuser u
left join tassoc a
on u.user_id = a.user_id
where u.restriction_id = 10
)
select distinct user_id from cte
with cte as
(
select u.*
from tuser u
where u.restriction_id = 10
)
select distinct user_id from cte
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
lildrc
Starting Member
8 Posts |
Posted - 07/07/2011 : 06:38:26
|
Both of those return just a single user_id.
Are they meant to run standalone or replace part of the initial query? |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 07:26:05
|
standalone.
If they return a single row then that's all you have in the table for that restriction_id.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
lildrc
Starting Member
8 Posts |
Posted - 07/07/2011 : 08:29:55
|
| Ok, I think I can ignore those users for now anyway - cheers for all your help. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 08:41:00
|
I think you should find out what happened to your data - you seem to have lost 427 users out of 428.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
lildrc
Starting Member
8 Posts |
Posted - 07/07/2011 : 09:45:50
|
This query:
with cte as
(
select u.*, g.group_name
from tuser u
join tassoc a
on u.user_id = a.user_id
join tgroup g
on g.group_id = a.group_id
where u.restriction_id = 10
)
select t.*,
csvstr = stuff (
(
select ',' + t2.group_name
from cte t2 where t.user_id = t2.user_id
for xml path('')
)
,1,1,'')
from (select distinct user_id, user_name, user_email from cte) t
still returns 423 users, but the others just seem to return 1. I'm not sure where the difference comes from! |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/07/2011 : 09:50:43
|
Are you saying that this returns one row
with cte as
(
select u.*
from tuser u
where u.restriction_id = 10
)
select distinct user_id from cte
how about
select distinct user_id
from tuser u
where u.restriction_id = 10
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
| |
Topic |
|
Joining data via an associations table
