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;
- Resolving the team ID to the team name (in an efficient way)
- 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
Best Answer
So, let's break this down into steps.
First, you need the earliest and latest
dateJoined
fromteamMembership
for each team. this can be done with a slight variation on what you already have: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 twoSELECT
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 samedateJoined
, we'll just list them once. If you really want to have the earliest and the latest dates when they are the same, useUNION 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
andinstructor
tables. This is why we includedteachingTeam.teamId
in our result set above; to facilitate that join.Note - all code is untested.
So, we use the
teamId
anddateJoined
from our original query to find the team members we want, and then usemember
to connect to theinstructor
table to pick up their first and last names.