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.
teamMembership with four columns;
teamMembership.team is linked to
teamMembership.member is linked to
instructor with 5 columns;
teachingTeam with three columns
teachingTeam.teamID is linked to
teachingTeam.leader is linked to