October 31, 2005 at 2:58 pm
i have 3 tables which are as follows:
tbl_users
ID INT PK
username varchar
password varchar
tbl_group_memberships
ID INT PK
userID
GroupID
tbl_groups
ID INT PK
groupName
GroupDescrip
i want to write a query which will return a list of all users from tbl_users and the group(s) they belong to.
but i am not sure how that query would look - any suggestions?
October 31, 2005 at 4:07 pm
select userID, username, groupName
from tbl_users U
Inner join tbl_group_memberships gm on U.ID = gm.userID
inner join tbl_groups G on gm.GroupID = G.ID
order by username
if a user belongs to many groups you will have 1 row for each group a user belongs to.
Please presnt some sample data, and how you want your output to look if this does not get you what you need
October 31, 2005 at 4:08 pm
select username, groupname
from tbl_users u
inner join tbl_groupmemberships gm on gm.UserID = u.ID
inner join tbl_groups g on gm.GroupID = g.ID
A.J.
DBA with an attitude
November 1, 2005 at 7:17 am
sorry i should have mentioned that if a user is a part of multiple groups i only what their first group membership returned. so there will only be one record per user.
basically, this is for a user manager where i just want to display a table containing a row for each user in the database, and i figured that i would make group membership a column in that output table.
November 1, 2005 at 9:18 am
select
u.username,
(select top 1 gm.groupname
from tbl_groupmemberships gm
where u.id=gm.userid
order by gm.id ) as 'groupname'
from
tbl_users u
where exists
(select gm.groupname
from tbl_groupmemberships gm
where u.id=gm.userid )
November 1, 2005 at 4:26 pm
hey thanks for the reply!
sorry but i am not sure what that last part is doing
where exists
(select gm.groupname
from tbl_groupmemberships gm
where u.id=gm.userid )
can you please explain that?
thanks!
November 1, 2005 at 4:36 pm
i ended up doing this and it seems to work:
SELECT tbl_users.username,
(select top 1 tbl_groups.groupname
from tbl_groups, tbl_group_memberships
where tbl_users.id=tbl_group_memberships.userid AND tbl_group_memberships.groupID=tbl_groups.ID
order by tbl_group_memberships.id) as 'GroupName'
FROM tbl_users
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply