Joining data via an associations table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Joining data via an associations table

Author

Topic

lildrc
Starting Member

8 Posts

Posted - 2011-07-07 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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 - 2011-07-07 : 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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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 - 2011-07-07 : 06:18:00

Hi,

That only returned one user with a null csvstring unfortunately.

Thanks for your help though.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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 - 2011-07-07 : 06:25:00

That just returned a single user_id for some reason.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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 - 2011-07-07 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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 - 2011-07-07 : 08:29:55

Ok, I think I can ignore those users for now anyway - cheers for all your help.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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 - 2011-07-07 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources