Hi Walter -
There are a couple of problems with the query.
First, the select statement must include the tablename along with the column name when joining tables.
Second, there is nothing in the Users table to indicate what group(s) a user belongs to and there is nothing in the Groups table to indicate what user(s) are members. As such, you have correctly deduced that you need to use the GroupMenbers table.
However, need to join the Users table to the GroupMembers table and join the Groups table to the GroupMembers table.
Given that each table has the following columns:
- Users: UserID, Username, Enabled, LoggedIn, SessionID, Email, SettingsTableVersion, FullName, Initials, UserData and InitialStatusID
- Groups: GroupID, GroupName, Description, AutoAdd
- GroupMembers: GroupID, UserID, ProjectID, IsMember
and looking at the query you started to write above, you want to list the Username from the Users table, the FullName from the Users table and the GroupName from the groups table.
Therefore your query should look like this:
SELECT Users.Username, Users.FullName, Groups.GroupName
FROM Users JOIN
GroupMembers ON Users.UserID = GroupMembers.UserID JOIN
Groups ON Groups.GroupID = GroupMembers.GroupID
WHERE Users.Enabled = 1
ORDER BY Users.Username
You will notice that I added a WHERE to filter out deleted users.
I recommend the following links to learn more about writing T-SQL queries:
Speak Like a T-SQL Developer
Writing Simple Select Statements
Writing Simple Select Statements - Part 2
Writing Simple Select Statements - Part 3
Joining Tables Part 1
Joining Tables Part 2
Joy, Thank you for the fast and accurate response. Also, thanks for the resources. I will take a look at these.
What a great set of resources, Joy! I was just wondering where to go to start rebuilding my knowledge of SQL (it's been way too many years).
Is there also a resource for learning about the structure of the EPDM databases?
Hi Brian -
Unfortunately, it's a bit of figuring out what you want.
I did a presentation at SWW and as part of the presentation I showed an entity relationship diagram (table map).
The entity relationship diagram is useful in showing you how tables are linked, what the table contains, etc)
I have included a zipped copy of the presentation. (includes: presentation, ER diagrams, query samples)
Now that is an awesome resource!