Need Distinct Value from Joined Tables in MSSQL

I have a sql statement that is pulling the information I need from the DB (joining 4 tables), except it is pulling duplicate values and I need distinct. I've tried several ways and cannot get it to pull only distinct (Group By, Distinct are two ways I've tried).

Would appreciate if someone could have a look at it. Below is the query and table structures.

Query
SELECT First_Name, Last_Name, Department_Name, suapp.FACULTY.Faculty_UUID, Faculty_Email, Faculty_Title, Faculty_Profile_Link FROM suapp.FACULTY
             
JOIN suapp.RESEARCH_MATCH
ON suapp.FACULTY.Faculty_UUID = suapp.RESEARCH_MATCH.Faculty_UUID

JOIN suapp.RESEARCH_AREA
       ON LEFT(suapp.RESEARCH_MATCH.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix 
 
 WHERE suapp.RESEARCH_AREA.Research_Area_Prefix = 'CD'";

Open in new window


This is an example of what it is presenting in the browser. (

Faculty Information and Research

The following list of faculty are involved in the CD.

Angela Madden, Associate Professor

Antonio Raciti, Assistant Professor

Antonio Raciti, Assistant Professor

Antonio Raciti, Assistant Professor

Antonio Raciti, Assistant Professor

Antonio Raciti, Assistant Professor

Antonio Raciti, Assistant Professor

Antonio Raciti, Assistant Professor

Brandon Ousley, Instructor

Brandon Ousley, Instructor

Brandon Ousley, Instructor

Brandon Ousley, Instructor

Brandon Ousley, Instructor

Charles Santo, Associate Professor

Charles Santo, Associate Professor

Charles Santo, Associate Professor

Charles Santo, Associate Professor

Charles Santo, Associate Professor

Charles Santo, Associate Professor

Charles Santo, Associate Professor

I'm attaching an example of my tables and the type of data in them. Any help is appreciated.
Debra
Debra TurnerWeb Specialist IIAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
the "rm" is an "alias" that applies to that whole subquery

this is the subquery

      SELECT DISTINCT
            suapp.RESEARCH_MATCH.Faculty_UUID
      FROM suapp.RESEARCH_MATCH
      JOIN suapp.RESEARCH_AREA ON LEFT(suapp.RESEARCH_MATCH.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix
      WHERE suapp.RESEARCH_AREA.Research_Area_Prefix = 'CD'


to JOIN that subquery we need some way to refer to it. That is done via the "alias"


JOIN (
      SELECT DISTINCT
            suapp.RESEARCH_MATCH.Faculty_UUID
      FROM suapp.RESEARCH_MATCH
      JOIN suapp.RESEARCH_AREA ON LEFT(suapp.RESEARCH_MATCH.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix
      WHERE suapp.RESEARCH_AREA.Research_Area_Prefix = 'CD'
 ) rm

from that point on you need to refer to any data from that subquery as "rm"

By the way, using a subquery this way is also known as a "derived table"

{+edit}

Aside from the alias discussed above, the reason you got "duplicates" in your original query is that a member of staff can be involved on more than one topic of research. This is a "one to many" relationship and SQL will automatically show multiple rows per member of staff.  What I did through the subquery was do remove possibility of multiple rows per staff member that could otherwise happen.
1
 
PaulCommented:
Does this help?
SELECT
      First_Name
    , Last_Name
    , Department_Name
    , suapp.FACULTY.Faculty_UUID
    , Faculty_Email
    , Faculty_Title
    , Faculty_Profile_Link
FROM suapp.FACULTY
JOIN (
      SELECT DISTINCT
            suapp.RESEARCH_MATCH.Faculty_UUID
      FROM suapp.RESEARCH_MATCH
      JOIN suapp.RESEARCH_AREA ON LEFT(suapp.RESEARCH_MATCH.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix
      WHERE suapp.RESEARCH_AREA.Research_Area_Prefix = 'CD'
  ) rm ON suapp.FACULTY.Faculty_UUID = rm.Faculty_UUID

Open in new window


perhaps also see: Select Distinct is returning duplicates ...
1
 
Debra TurnerWeb Specialist IIAuthor Commented:
Hi Portlet Paul,

What is the 'rm' you added in there (is that a name for the Research Match table)? and I see you put the DISTINCT in a new SELECT which I tried, but didn't have it in the right table (apparently!)

Thank you so much! This works perfectly.
Debra
0
 
Debra TurnerWeb Specialist IIAuthor Commented:
Hi Portlet Paul,
Can I ask how you came up with the name 'rm'? Is it Research Match? Just curious. I have a related question I will be posting in a few minutes.
Thank you,
Debra Turner
0
 
Debra TurnerWeb Specialist IIAuthor Commented:
Dear PortletPaul,
Thank you for giving such an easy to understand answers to my question! Sometimes it is overwhelming when you google something and you get 1000 different variations of an answer.
Debra Turner
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.