1

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

0

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.

  • Thanks for the detailed explanation. A couple questions to make sure I understand all the code correctly. We can declare the aliases - not sure if that's the right term (teachingTeam tt) anywhere? Is it declared as tmbr and mbr in different cases similarly to scope of variables in other languages? Why do we use the as dateJoined? – cDU57 Feb 15 '18 at 23:56
  • Table aliases have to be set in the FROM clause. A subquery can refer to a table column of an outer query, so it's a bad idea to use the same alias for a table in a subquery and a query outside that subquery. I can't say it won't work - but I like my code to be as clear as possible, and to be obvious as to how it should work. – RDFozz Feb 16 '18 at 15:52
  • MIN(tmbr.dateJoined), in the subquery, is a column - but, as the result of a function, it has no name. Since it's basically a specific dateJoined value, it's simplest to name the resulting column dateJoined again. Columns that you aren't go to refer to elsewhere can be unnamed; however, we needed to refer to the column outside the sub-query, so we had to give it a name. The as isn't required (as you'll note, I don't use it with table aliases), but I always use it with column aliases. – RDFozz Feb 16 '18 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.