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