Display subquery columns in result

Question

I'm new to SQL (also my first question) and have been trying to determine the best practises and most efficient way to code my problem. I'm using mySQLWorkbench on MacOS. I'm trying to get the result of an aggregate query across multiple tables. I need to display the first and last name, team name, earliest and latest instructor for each team. The following query gives me the first and last names of the correct instructors. The comments are what I tried to do, but the WHERE clause can only accept one operand.

SELECT i.firstname ,i.lastname -- , tt.name tm.dateJoined 
FROM instructor i --, teachingTeam tt, teamMembership tm
WHERE id IN --and tt.name and tm.dateJoined IN
    (
        SELECT member --, team, dateJoined
        FROM teamMembership AS t
        WHERE datejoined = (
                SELECT min(dateJoined)
                FROM teammembership AS t2
                WHERE t2.team = t.team
                )
            OR dateJoined = (
                SELECT MAX(datejoined)  
                FROM teammembership AS t2
                WHERE t2.team = t.team
                )
    );

I have the bulk of the query down I'm just having 2 issues;

1. Resolving the team ID to the team name (in an efficient way)
2. I'd like to output the dateJoined from the subquery rather than write an entire other query (seems redundant)

I believe that I can use WHERE EXISTS however I had trouble understanding it

I have a three main tables I need to work with.

One called teamMembership with four columns;

team member dateJoined and dateLeft

teamMembership.team is linked to teachingTeam.teamID,

teamMembership.member is linked to instructor.id

instructor with 5 columns;

id lastName firstName dateOfBirth and email

lastly, teachingTeam with three columns

teamID name and leader

teachingTeam.teamID is linked to teamMembership.team

teachingTeam.leader is linked to instructor.id

Answer 1

So, let's break this down into steps.

First, you need the earliest and latest dateJoined from teamMembership for each team. this can be done with a slight variation on what you already have:

SELECT tt.teamId, tt.name, MIN(tmbr.dateJoined) as dateJoined
  FROM teachingTeam tt
         INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
 GROUP BY tt.name
UNION
SELECT tt.teamId, tt.name, MAX(tmbr.dateJoined) as dateJoined
  FROM teachingTeam tt
         INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
 GROUP BY tt.name
;

GROUP BY says to give us the MIN (or MAX) dateJoined for all the rows for each specific team.

UNION says to take the results of the two SELECT queries, and treat them as a single result set. It also gets rid of duplicates, so if (for instance) all the members of one team have the same dateJoined, we'll just list them once. If you really want to have the earliest and the latest dates when they are the same, use UNION ALL instead.

Now, you want to list the instructors who joined each team on the dates indicated. The simplest way to do that is to treat our first result set as if it were a table, and then to join that data to our teamMembership and instructor tables. This is why we included teachingTeam.teamId in our result set above; to facilitate that join.

SELECT djt.name, mbr.dateJoined, i.firstname, i.lastname
  FROM (SELECT tt.teamId, tt.name, MIN(tmbr.dateJoined) as dateJoined
          FROM teachingTeam tt
                 INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
         GROUP BY tt.name
        UNION
        SELECT tt.teamId, tt.name, MAX(tmbr.dateJoined) as dateJoined
          FROM teachingTeam tt
                 INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
         GROUP BY tt.name
       ) djt  -- for Date Joined Team
         INNER JOIN teamMembership mbr ON (    djt.teamId = mbr.team
                                           AND djt.dateJoined = mbr.dateJoined)
           INNER JOIN instructor i ON (mbr.member = i.id)
 ORDER BY djt.name, mbr.dateJoined
;

Note - all code is untested.

So, we use the teamId and dateJoined from our original query to find the team members we want, and then use member to connect to the instructor table to pick up their first and last names.