Logan Buesching

So I have been doing some more Gnary Queries for MyEPICS.

We have a table called TeamChoice that has 3 columns, userid, teamid, and choice. These represent a person’s 5 choices that they make when registering for EPICS. There is one special row for each person. If the person is not assigned to a team, they are given a choice=0 with their current team choice. So with 5 team choices, each user has 6 rows in the database.

The Scenario

What I wanted to do is find all the students who are currently in their first team’s bucket. So that means that for each user, I wanted to get the teamid where choice=0, and compare it to where choice=1. If they are the same teamid, I wanted to return the result. This probably could be accomplished by some script, but a database should be able to give me the results w/o any scripting.

The Method

The way to accomplish this is through subqueries. I realized that I am essentially querying against two tables, then doing a join on the userid. The first table all the records for students and their first choice, the second table is the records for students and their current choice. If I then join the two tables together on their userid, then I will have what their first choice is, and their current choice. All I need to do next is add a where clause that makes sure that I only get records with the two results are the same and it’s over.

The Query

SELECT User.username
FROM (
SELECT TeamChoice. *
FROM User
INNER JOIN TeamChoice ON User.id = TeamChoice.userid
WHERE TeamChoice.choice =1
) AS firstchoice
INNER JOIN (
SELECT TeamChoice. *
FROM User
INNER JOIN TeamChoice ON User.id = TeamChoice.userid
WHERE TeamChoice.choice =0
) AS currentchoice
ON currentchoice.userid = firstchoice.userid
INNER JOIN User ON User.id = currentchoice.userid
WHERE firstchoice.teamid = currentchoice.teamid