November 2, 2014 at 11:31 pm
I was wondering if anyone can help with a query I need for a larger series of reports I am working on.
Team members appear twice or more if they belong to more than one team. I need to be able to show their name and main team. Team is not important at the moment but I just like to include all team members and display a team name against them.
This is what I have at the moment:
SELECT SystemUser.systemuserid, FullName, TeamMembership.TeamID, TeamName
FROM Team
RIGHT OUTER JOIN TeamMembership ON Team.teamid = TeamMembership.teamid
LEFT OUTER JOIN SystemUser ON TeamMembership.systemuserid = SystemUser.systemuserid
order by FullName
I think I need to use group by clause but I'm not sure, any assistance would be grateful. 🙂
Thanks Brian
You are never an expert, you are always learning!November 3, 2014 at 9:35 am
How would you determine which team is the "main" one for a person? Not sure how you would remove duplicates if you have a relationship like this:
Person---(1,M)--Membership--(M,1)---Team
unless you had a column in Membership that indicated "main"/"primary".
If you put the Person in a Group in your report, you could hide the duplicates.
November 3, 2014 at 7:55 pm
They is no flag as such but there is a default team which if they belong to two teams is not their main team.
What I did was write two select statements, one working out who belongs to two groups and displaying the non default team, the other users who only belong to the default team and then union them together.
This works, but is there a more elegant way?
Thanks Brian
You are never an expert, you are always learning!November 4, 2014 at 12:04 pm
Brian Ward (11/3/2014)
They is no flag as such but there is a default team which if they belong to two teams is not their main team.What I did was write two select statements, one working out who belongs to two groups and displaying the non default team, the other users who only belong to the default team and then union them together.
This works, but is there a more elegant way?
How does one determine this default team? How do I determine which of the two or more teams someone might have would be the default?
As of right now there's just not enough info provided to give you any form of recommendation. Would it be possible to provide the table definitions involved (bonus points for actual test data to boot)?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply