Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need Distinct Value from Joined Tables in MSSQL

Posted on 2016-11-28
5
Medium Priority
?
105 Views
Last Modified: 2016-11-30
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
0
Comment
Question by:Debra Turner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41904837
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
 

Author Comment

by:Debra Turner
ID: 41904920
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41904934
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
 

Author Comment

by:Debra Turner
ID: 41907364
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
 

Author Closing Comment

by:Debra Turner
ID: 41907368
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question