April 25, 2016 at 1:44 pm
All:
I have two tables as defined below (I know this isn't the complete script to create the tables, but thought showing columns is all that would be needed)
CREATE TABLE [dbo].[GroupAssignment](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GroupID] [int] NOT NULL,
[UserID] [int] NOT NULL,
CREATE TABLE [dbo].[UserGroup](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](255) NOT NULL,
[Full_Name] [nvarchar](100) NULL,
[date_created] [datetime] NULL,
[IsGroup] [bit] NULL,
Table GroupAssignment simply holds the links between a user and the groups assigned to them. The problem is: UserGroup holds both users and groups in the same table. Groups are defined as having "IsGroup = 1" and all others (NULL, 0) are defined as users. The join between the tables depends upon whether we want to show Users or Groups but UserGroup.ID is the key for both columns of GroupAssignment.UserID and GroupAssignment.GroupID.
I have been asked to produce a list of usernames and their assigned groups. Column UserGroup.Name holds both the username and the group name (UserName if "IsGroup <> 1" and GroupName if "IsGroup = 1").
Hoping someone can provide some help, as this join is frustrating me greatly.
I am having a dickens of a time getting these self joins to produce the necessary data and was hoping someone could help.
April 25, 2016 at 3:19 pm
It is not required to post the full CREATE TABLE statements; simplifying by removing columns that are not used in the problem is okay (and as far as I'm concerned even encouraged).
However, I do prefer the posted scripts to actually run. I am fine volunteering my time to help you debug code you are stuck with, but I prefer not to spend time solving extra problems because you leave out closing parentheses and who knows what else.
Please post again using a repro script that can be executed: CREATE TABLE statements including constraints and indexes (but feel free to omit irrelevant columns), INSERT statements with sample data, the results you expect from that sample data, and the partial work you already did before you got stuck. Test that the script you post runs without error on your server, and if your server is case insensitive do a manual double check of upper- vs lowercase of table and column names.
Such a post allows me and others to quickly copy and paste your code, zoom in on the area you need help with, find a solution, test it and post it.
April 25, 2016 at 4:17 pm
Try code something like below.
Btw, the GroupAssignment table does not need an identity column. The unique clustering key should be either ( GroupID, UserID ) or ( UserID, GroupID ) depending on how the table is used more often. This looks like the "default" of slapping an identity column on every table, and worse, "automatically" clustering on it, both of which are horrible practices.
SELECT ug_user.Name AS User_Name, ug_group.Name AS Group_Name
FROM dbo.UserGroup ug_user
INNER JOIN dbo.GroupAssignment ga ON ga.UserID = ug_user.ID
INNER JOIN dbo.UserGroup ug_group ON ug_group.ID = ga.GroupID
WHERE ug_user.IsGroup IS NULL OR ug_user.IsGroup = 0
ORDER BY User_Name, Group_Name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply