Skip to content

Instantly share code, notes, and snippets.

@matthewpoer

matthewpoer/account_team_info

Created February 19, 2015 20:13
Show Gist options
  • Save matthewpoer/60eb43ff461f5fdd2ffe to your computer and use it in GitHub Desktop.
Save matthewpoer/60eb43ff461f5fdd2ffe to your computer and use it in GitHub Desktop.
Download ZIP
Queries to reveal an Account record's Teams and Users through the Team Set (team_set_id)
Raw
account_team_info
-- Reference: Query to fetch information for a specific Account's teams
-- (ignores team_id, focus on team_set_id)
select accounts.id, accounts.name, accounts.team_id, accounts.team_set_id,
teams.id, teams.name, teams.description
from accounts
join team_sets on accounts.team_set_id = team_sets.id and team_sets.deleted=0
join team_sets_teams on team_sets_teams.team_set_id = team_sets.id and team_sets_teams.deleted=0
join teams on teams.id = team_sets_teams.team_id and teams.deleted=0
where accounts.id = 'SOMEACCOUNTID' and accounts.deleted=0;
-- Reference: Query to fetch information for a specific Account's teams
-- (ignores team_id, focus on team_set_id) *and* find the Users
-- associated to each of these teams
select accounts.id as account_id, accounts.name as account_name,
accounts.team_set_id as team_set_id,
teams.id as team_id, teams.name as team_name,
users.id as user_id, users.user_name as user_name,
team_memberships.*
from accounts
join team_sets on accounts.team_set_id = team_sets.id and team_sets.deleted=0
join team_sets_teams on team_sets_teams.team_set_id = team_sets.id and team_sets_teams.deleted=0
join teams on teams.id = team_sets_teams.team_id and teams.deleted=0
join team_memberships on team_memberships.team_id = teams.id and team_memberships.deleted=0
join users on users.id = team_memberships.user_id and users.deleted=0
where accounts.id = 'SOMEACCOUNTID' and accounts.deleted=0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment