sql - MySQL - Finding Empty relations -

i trying work out how full list of team leaders , team members our tables. including team leaders have no team.

we have basic data structure have table person contains our basic person object. people extend table in details table.

one of extended pieces of information have team_leader status. have 2 relevant details define this: detail_isteamleader , detail_teamleader. first boolean , defines short list of team leaders. second defines persons team leader (ideally subset).

_person table_ person_id person_name  _detail table_ detail_id detail_person_id detail_isteamleader detail_teamleader 

i can team leaders have team using following query:

select tl.person_id tlref, tl.person_name tlname,  per.person_id perref, per.person_name pername detail left outer join person per on detail.person = per.person_id left outer join person tl on detail_teamleader = tl.person_id order tlname, psname 

however, fails team_leaders detail.isteamleader true not occur in detail_teamleader field.

sample data

person_id   person_name 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 

in example above joe , fred team leaders. however, joe has no-one in team, , in query above therefore omitted result set.

i suspect normalizing data structure might best answer, there way without doing this?

i understand have 1 1 relationship between persons , details not persons have detail information.

i'm not sure if want team leaders persons in team (including ones don't have team), or want dataset persons team leader information.

in first case have use inner select (or cte in sql server) select team leaders , join details , persons tables. here's query use:

select tl.person_id tlref, tl.name tlname, p.person_id perref, p.name pername  (         select p.person_id,p.name          @persons p          inner join @details d on d.person_id = p.person_id , 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 second case (getting persons team leader information):

select p.person_id perref, p.name pername, d.is_teamleader,  ptl.person_id tlref, ptl.name tlname @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 first query asking added second 1 in case need it. please note used table variables (@persons, @details) replace actual table names.