查找两个不同成员都参与的活动

In a system, there are Events, EventRosters, Teams, TeamRosters, Members, and Gamertags.

Events: Video game events, can be solo (1v1) or team (team vs team) (Example columns: id, type(solo/team), participants, starting time, prize, etc).

EvenRosters: Roster of gamertags playing in this event regardless if it's a team or solo event (if team event, team!=0) (Example columns: id,event,gamertag,team)

Teams: Table consisting of team data (Example columns: id, team name, created date, public/private, icon file name, etc)

TeamRosters: Table consisting of member IDs belonging to said team (Example columns: id, team, member)

Members: Table consisting of member data (Example columns: id, email, username, etc)

Gamertags: Table consisting of gamertags (for xbox, playstation, etc) belonging to members (Example columns: id, gamertag, member)

For a solo event:

Events:

• ID = [Auto Increment]
• Type = 0 (0 = solo, 1 = team)
• Challenger = Challenger_Member_ID (participant 1)
• Contestant = Contestant_Member_ID (participant 2)
• Starting time = time()
• Prize = 40 ($40USD)

EventRosters:

• ID = [Auto Increment]
• Team = 0 (0 = solo roster)
• Gamertag = Challenger_Gamertag_ID
• Event = Event_ID

Then for a team event:

Events:

• ID = [Auto Increment]
• Type = 1 (0 = solo, 1 = team)
• Challenger = Challenger_Team_ID (participant 1)
• Contestant = Contestant_Team_ID (participant 2)
• Starting time = time()
• Prize = 100($100USD)

EventRosters:

• ID = [Auto Increment]
• Team = Challenger_Team_ID (0 = solo roster)
• Gamertag = Challenger_Team_Participant_Gamertag_ID
• Event = Event_ID

So, fetching solo events are simple enough because the Member_ID is available directly from the Event.Challenger or Event.Contestant columns. However, if we want to know member names of those who participated in a team event, or if we want to search team events that a certain member participated in, we have a few choices.

Question: Is it possible to fetch this data in a single query?

Purpose: Find team events that two different specified members both participated in

METHOD 1 : START WITH MEMBER GAMERTAGS
// Fetch team events that two different members both participated in.
$memberID = 3;
$secondMemberID = 5;

$memberGamertags = $pdo->Query('SELECT id FROM member_gamertags WHERE member='.$memberID);
$st = $pdo->Prepare('SELECT event,team FROM event_rosters WHERE team=0 AND gamertag=?');
foreach($memberGamertags as $gt) {
  $st->Execute([$gt['id']]);
  $rosterFile = $st->Fetch();
  $event = $pdo->Query('SELECT id,challenger,contestant FROM events WHERE id='.$rosterFile['event'])->Fetch();
  // check if proper team
  $otherTeamID = ( $event['challenger'] == $rosterFile['team'] ) ? $event['contestant'] : $event['challenger'];
  // now search that team for other member
  $otherTeamRoster = $pdo->Query('SELECT * FROM team_rosters WHERE member='.$secondMemberID.' AND team='.$otherTeamID)->Fetch();
  if ( $otherTeamRoster ) // found an event they both participated in
    $foundEvents[] = $event;
}
//
// REPEAT FOR OTHER MEMBER
//