Dan Kelly Dan Kelly - 10 months ago 58
MySQL Question

MySQL - Finding Empty relations

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

which contains our basic person object. For certain people we extend this table with in a

One of the extended pieces of information that we have is team_leader status. We have two relevant details that define this:
The first is a boolean and defines a short list of team leaders. The second defines who a persons team leader is (ideally from the subset).

_person table_

_detail table_

I can get all the team leaders who have a team using the following Query:

SELECT tl.person_id AS tlref, tl.person_name AS tlname,
per.person_id AS perref, per.person_name AS pername
FROM mf_details
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

However, this fails for those team_leaders where
is true but do not occur in the

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 the example above Joe and Fred are Team Leaders. However, Joe has no-one in his team, and in the query above is therefore omitted from the result set.

I suspect that normalizing the data structure might be the best answer, but is there a way without doing this?

Answer Source

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.