CODEWITHSUNDEEP
mysqlsqldatabase
XanderStrike
XanderStrike

Reputation: 726

How to distinct on a single column in a query in SQL?

I have a webapp for a school that pulls the locations of both students and faculty when they register for the system. These locations change over time, though (students change dorm, staff switch buildings), so I'm building a script to update them occasionally. I need to run a query that pulls in a list of userids and building names, and I'd like to do it in a single query for personal edification/masochistic reasons.

I have access to a central database that has the following (simplified) schema:

facstaff(userid, bldgcode)
students(userid, dorm)
buildings(bldgcode, building) # building is the human readable building name

and the query that I'm working with so far looks like this:

SELECT users.userid, buildings.building 
FROM (SELECT userid, bldgcode FROM facstaff 
      UNION 
      SELECT userid, dorm AS bldgcode FROM students)
     AS users
     INNER JOIN buildings ON users.bldgcode=buildings.bldgcode 
WHERE userid in (<list of users>);

 

The issue I'm having is that many students go on to work in the school after they graduate (student entries are not removed from the db on graduation), or will work and study simultaneously, so there are duplicate userid after my union, but they have different bldgcode so a simple distinct doesn't work. Basically I get results like this:

+----------+-----------------------+
| userid   | building              |
+----------+-----------------------+
| jimbo    | Science Sphere        |
| billiam  | Dorm E11              |
| dwayne   | Humanities Hall       |
| mandar   | Science Sphere        |
| foobaz   | Administration        |
| fredrick | Physical Plant        |
| fredrick | Dorm A6               |
+----------+-----------------------+

Fredrick is both a student and a staff member, so two locations come up with his name; one from facstaff and one from students. Ideally, there would be a way to enforce distinction in the userid column so that the building coming from facstaff would override anything coming out of students. I'm open to other solutions though, of course. I appreciate any help!

Upvotes: 1

Views: 68

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

To rephrase what you're looking for, if a user is both faculty and a student, that you want the faculty information to take precedence over the student information.

To do this, you can add a check to the students part of your UNION to only return a student if they aren't also a faculty member - implemented here with LEFT JOIN / IS NULL:

SELECT users.userid, buildings.building 
FROM (
  SELECT userid, bldgcode FROM facstaff 
  UNION 
  SELECT userid, dorm AS bldgcode FROM students
  LEFT JOIN facstaff ON facstaff.userid = students.userid
  WHERE facstaff.userid IS NULL
) AS users
  INNER JOIN buildings ON users.bldgcode=buildings.bldgcode 
WHERE userid in (<list of users>);

Upvotes: 4

Related Questions