存在取决于类似记录的返回值

I'm working lists of members in a team. The team can have 1 leader or 2 co-leaders.

Table: Position

Position_ID | Position_Name

Table: Involvement

Position_ID | Project_ID | Person_ID

In the Position table, Position_ID '7' has the Position_Name 'Leader / Co-leader'.

Suppose there is John (Person_ID = 5) who takes part in project with Project_ID = 2 and in that project he has Position_ID = 7.

• If there is Jim (Person_ID = 9) who also takes part in that project (Project_ID = 2) and also serves at that position (Position_ID = 7), the position for John with Person_ID = 5 is 'Co-Leader'. Return array(person_id => 5 , name => John, position => Co-leader, position_id => 7, project_id => 2)

• If there is not, the position for John is 'Leader'. Return array(person_id => 5 , name => John, position => Leader, position_id => 7, project_id => 2)

My reasoning so far:

• Select from Involvement, join Member to get member details and join Position to get position name.

• If the position ID is not 7, which means it's not the leader / co-leader case, then let it be.

• If the position ID is 7, check in Involvement if there exists a record with the same position ID and project ID but different person ID.

• If there is, cut the latter half out of the Position #7 name and return 'co-leader' only. If there isn't, cut the former half and return 'leader' only.

Problem: I don't know how to express "the person id of another record is different from the person id of the current record".

Simplified MySQL

SELECT 
    IF(`i`.position_id = 7, 
        IF(EXISTS(SELECT 1 FROM `i` WHERE ????),
           SUBSTRING...,
           SUBSTRING...)
        ,po.`name`) 
    AS `position`, 
    FROM `involvements` i 
    JOIN `members` m ON m.`id` = i.`person_id`
    JOIN `positions` po ON po.`id` = i.`position_id`
    WHERE i.`project_id` = 2 

Maybe this will get you moving in the right direction.. But as I don't understand your view of desired data layout... it may not be exactly what you're after.

The join simply identifies if there are two people on the same project with the same position 7. If any records are found, then those records are co-leaders which is identified in the case statement.

SELECT i.person_Id
   , m.name as MemberName
   , case when i2.po.position_ID is null then po.name 
          else 'Co-Leader' end as Position
FROM `involvements` i 
JOIN `members` m 
  ON m.`id` = i.`person_id`
JOIN `positions` po 
  ON po.`id` = i.`position_id`
LEFT JOIN involvements i2 
  on i2.`position_Id` = po.`position_ID`
 and po.position_ID = 7
LEFT JOIN members m2 
  on m2.`id` = i2.`personID`
 and m2.`person_ID` <> m.`person_ID`
WHERE i.`project_id` = 2 

Left join the positions table once more where the project id and position id are the same, but take only those records that have a position id of 7 (leader) and the person id does not match. If there is no such record (values from that table will be null), then it's a leader, otherwise it's a co-leader.

SELECT 
    IF(i2.person_id is null,'leader','co-leader') 
    AS `position`, 
    FROM `involvements` i 
    INNER JOIN `members` m ON m.`id` = i.`person_id`
    INNER JOIN `positions` po ON po.`id` = i.`position_id`
    LEFT JOIN `involvements` i2 on i.project_id=i2.project_id and i.position_id=i2.position_id and i.person_id<>i2.person_id and i2.position_id=7
    WHERE i.`project_id` = 2