Queries to reveal an Account record's Teams and Users through the Team Set (team_set_id)

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;