I am somewhat new to SQL queries. Limited exposure over the years but am working on learning more.
I created the query below. Instead of presenting GroupID in the results, I would like to return the corresponding Groupname from the Groups table. How do I do that with this query?
select Username, FullName, GroupID from Users join GroupMembers on Users.UserID=GroupMembers.UserID order by Username
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:
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