simple SQL query question

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:

Video Resources

Speak Like a T-SQL Developer

§http://www.sqlshare.com/Player.aspx?vid=35

Writing Simple Select Statements

§http://www.sqlshare.com/Player.aspx?vid=45

Writing Simple Select Statements - Part 2

§http://www.sqlshare.com/Player.aspx?vid=49

Writing Simple Select Statements - Part 3

§http://www.sqlshare.com/Player.aspx?vid=52

Joining Tables Part 1

§http://www.sqlshare.com/Player.aspx?vid=64

Joining Tables Part 2

§http://www.sqlshare.com/Player.aspx?vid=74

Regards,

Joy