CODEWITHSUNDEEP
sql
mcclosa
mcclosa

Reputation: 1445

SQL - Remove Duplicates in Single Field

SELECT Company.CompanyName
      ,Student.Status
      ,Student.Level
      ,Student.PlacementYear
      ,Company.CompanyCode
      ,Company.HREmail
      ,Company.Telephone
      ,Company.HRContact
      ,PlacedStudents.DateAdded 
FROM Student 
    RIGHT JOIN (Company INNER JOIN PlacedStudents 
                        ON Company.CompanyCode = PlacedStudents.CompanyCode) 
           ON Student.StudentNo = PlacedStudents.StudentNo 
WHERE (((Student.PlacementYear)=" & Year & ")) 
    AND((Student.Status)<>'Still Seeking YOPE') 
ORDER BY Company.CompanyName

I have this SQL Query which pulls HR Contacts from Companies where students are currently placed. However, there are multiple students at one company so when I run the query there are duplicates. I'm fairly new to SQL, I tried DISTINCT, however it didn't seem to do anything, the duplicates remained.

How can I remove duplicates in the CompanyCode field so that the Company only appears once when the query is run.

Below is an image of what happens when I run query. Hopefully this makes sense? http://s3.postimg.org/jarduw9pf/REPORT.png

Any help would be appreciated.

Upvotes: 0

Views: 79

Answers (2)

Bulat
Bulat

Reputation: 6969

This query should give you companies that have placed students:

SELECT Company.CompanyName
      ,Company.CompanyCode
      ,Company.HREmail
      ,Company.Telephone
      ,Company.HRContact
FROM Company 
WHERE EXISTS (SELECT * FROM PlacedStudents INNER JOIN 
               Student ON Student.StudentNo = PlacedStudents.StudentNo 
                        WHERE Company.CompanyCode = PlacedStudents.CompanyCode
                         AND Student.PlacementYear =" & Year & " 
                         AND Student.Status <>'Still Seeking YOPE')
ORDER BY Company.CompanyName;

Upvotes: 1

Dave Brown
Dave Brown

Reputation: 500

Your question is asking for HR Contacts from Companies where students are placed. I assume this means if you have 1, 2 or 1,000,000 students at a single company, you only want to see the company listed once?

Your current query is returning information from STUDENT and PLACEDSTUDENTS which is going to result in output like

COMPANY_A STUDENT01 .........
COMPANY_A STUDENT02 .........
COMPANY_A STUDENT03 .........

and so on.

If so, and taking a best guess (since I can't know what's in STUDENT or PLACEDSTUDENTS tables), try not including anything related to STUDENT in the SELECT.

SELECT DISTINCT Company.CompanyName, Company.CompanyCode, Company.HREmail, 
Company.Telephone, Company.HRContact FROM

I'll be happy to help more if you can provide more information about the structure of the tables and some examples of data, AND what you actually want from the query.

Upvotes: 0

Related Questions