Solved

Need Distinct Value from Joined Tables in MSSQL

Posted on 2016-11-28
5
23 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
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now