SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining data via an associations table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  05:47:24  Show Profile  Reply with Quote
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
3383 Posts

Posted - 07/07/2011 :  05:53:52  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  05:57:59  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  06:05:57  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  06:11:12  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  06:18:00  Show Profile  Reply with Quote
Hi,

That only returned one user with a null csvstring unfortunately.

Thanks for your help though.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  06:22:26  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  06:25:00  Show Profile  Reply with Quote
That just returned a single user_id for some reason.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  06:29:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  06:38:26  Show Profile  Reply with Quote
Both of those return just a single user_id.

Are they meant to run standalone or replace part of the initial query?
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  07:26:05  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  08:29:55  Show Profile  Reply with Quote
Ok, I think I can ignore those users for now anyway - cheers for all your help.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  08:41:00  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

lildrc
Starting Member

8 Posts

Posted - 07/07/2011 :  09:45:50  Show Profile  Reply with Quote
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!
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/07/2011 :  09:50:43  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000