I am trying to work out how to get a full list of team leaders and team members from our tables. Including those team leaders who have no team.
We have a basic data structure where we have a table
SELECT tl.person_id AS tlref, tl.person_name AS tlname,
per.person_id AS perref, per.person_name AS pername
LEFT OUTER JOIN person AS per ON detail.person = per.person_id
LEFT OUTER JOIN person AS tl ON detail_teamleader = tl.person_id
ORDER BY tlname, psname
1 Joe Bloggs
2 Fred Smith
3 Frank Jones
4 Amy Kelly
detail_id detail_person_id detail_isTeamLeader detail_teamLeader
1 1 1 0
2 2 1 0
3 3 0 3
4 4 0 3
I understand that you have a 1 to 1 relationship between the persons and details but not all persons have detail information.
I'm not sure if you want to get only the team leaders with all persons in their team (including the ones that don't have a team), or you want a dataset with all persons with their team leader information.
In the first case you would have to use an inner select (or CTE in Sql server) to select all your team leaders and join that with details and persons tables. Here's the query you could use:
select tl.person_id as tlref, tl.name as tlname, p.person_id as perref, p.name as pername from ( select p.person_id,p.name from @persons p inner join @details d on d.person_id = p.person_id and d.is_temamleader = 1 ) tl left outer join @details d on d.team_leader = tl.person_id left join @persons p on p.person_id = d.person_id
For the second case (getting all persons with their team leader information):
select p.person_id as perref, p.name as pername, d.is_teamleader, pTl.person_id as tlref, ptl.name as tlname from @persons p left outer join @details d on d.person_id = p.person_id left outer join @persons pTl on pTl.person_id = d.team_leader
I suspect the first query is what you were asking for but I added the second one just in case you need it. Please note that I used table variables (@persons, @details) so replace those with your actual table names.