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
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)
Best Regards,
Joy
Now that is an awesome resource!
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