many to many query - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

many to many query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-21 : 08:47:37

Jeffery writes "I have a table called tblusers. It has user_id_pkey, user_first_name, user_last_name, user_password, etc..

I have a table called tblgroups. It has group_id, group_name.

Then I have a table called tblusergroups that associate the user id to one or more groups.
fields: usergroup_id, user_id_fkey, group_id_fkey.

What I am trying to do is select a list of users that have the same groups as a specific user. This is the query that I tried:

SELECT user_id_pkey, (user_first_name + ' ' + user_last_name) AS userfullname, group_id_fkey
FROM v_usergroups_get
WHERE user_id_pkey <> @userid and group_id_fkey LIKE (SELECT tblusergroups.group_id_fkey FROM tblusergroups WHERE user_id_fkey=@userid)
ORDER BY userfullname;

v_usergroups_get is a sql view of:

SELECT tblusers.user_id_pkey, tblusers.user_first_name,
tblusers.user_last_name, tblusers.user_active,
tblusergroups.group_id_fkey
FROM dbo.tblusers LEFT OUTER JOIN
dbo.tblusergroups ON
dbo.tblusers.user_id_pkey = dbo.tblusergroups.user_id_fkey
WHERE (dbo.tblusers.user_active = 1)

This only works if there is only one row in the:

group_id_fkey LIKE (SELECT tblusergroups.group_id_fkey FROM tblusergroups WHERE user_id_fkey=@userid)

part of the Where part of the statement. otherwise I get an error, can't have more than one row...

I tried using the code in the article about converting data to a CSV:

Declare @userid int
Set @userid = 1

DECLARE @GroupList varchar(100)

SELECT @GroupList = COALESCE(@GroupList + ', ', '') + CAST(group_id_fkey AS varchar(5))
FROM v_usergroups_get
WHERE user_id_pkey = @userid;

SELECT user_id_pkey, (user_first_name + ' ' + user_last_name) AS userfullname, group_id_fkey
FROM v_usergroups_get
WHERE user_id_pkey <> @userid and group_id_fkey IN (Select @GroupList)
ORDER BY userfullname;

I get an error saying that it can convert char to int. I then used CAST on the group_id_fkey in the where clause. I didn't get any error but it didn't find any records either. I know there is atlease one record with the correct info to meet my query.

I have sql 7 sp4 on windows 2000 sp3.

Thanks for your Help.

JefferyS"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-21 : 12:57:44

something like

select distinct user_id
from tblUsers u,
where not exists
(select *
from tblUserGroups ug
left outer join tblUserGroups ug2
on ug.group_id_fkey = ug2.group_id_fkey
and ug2.user_id = u.user_id
where ug.user_id = @user_id
and ug2.user_id is null
)

==========================================
Cursors are useful if you don't know sql.
DTS 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