[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sql select distinct statement

Posted on 2012-03-14
7
Medium Priority
?
378 Views
Last Modified: 2012-06-21
I get duplicates of a s.Staff_ID.  How can i remove duplicates of s.Staff_ID?
Staff_ID is a INT data type and the primary key of the Staff table.  I tried "select distinct" but still had duplicate Staff_ID's in the results.  

 sql = "SELECT     l.Staff_Training_ID, s.Staff_ID, s.Staff_LName, s.Staff_MName, s.Staff_FName, s.hphone, s.Hcounty, s.hstreet1, s.hstreet2, s.hcity, s.hstate, s.hzip, s.Emp_DOB, l.Child_Abuse_Dt, l.Food_Handler_Exp_Dt, l.First_Aid_Exp_Dt, l.CPR_Exp_Dt, l.DMV_Record_Dt, l.Drivers_License_Exp_Dt, s.CBR_Comments, CBR_Exp_DT, fsa.Facility_ID, f.Director_Name, f.Site_Phone_No FROM Staff_Training_Log AS l INNER JOIN Staff AS s ON l.Staff_ID = s.Staff_ID AND l.Staff_ID = s.Staff_ID INNER JOIN Facility_Staff_Assoc AS fsa ON l.Staff_ID = fsa.Staff_ID INNER JOIN Facility AS f ON f.Facility_PK = fsa.Facility_ID WHERE facility_ID = " & lv_Facility_ID & " ORDER BY s.Staff_LName;"

Open in new window


I really appreciate your assitance.
0
Comment
Question by:ocdc
[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
7 Comments
 
LVL 8

Expert Comment

by:Crashman
ID: 37722947
that's because the combinations (in the complete table) return distinct values
if one column is distinct of the entire table, sql will show it
take a look

SELECT internal.i FROM (
SELECT 1 AS i
UNION  
SELECT 2 AS i
union
SELECT 3 AS i
) internal



SELECT distinct internal.i, internal.o FROM (
SELECT 1 AS i, 2 AS o
UNION  
SELECT 1 AS i, 1 AS o
union
SELECT 3 AS i, 2 AS o
) internal

Open in new window


try this

SELECT 
	Staff_Training_ID
    , Staff_ID
    , Staff_LName
    , Staff_MName
    , Staff_FName
    , hphone
    , Hcounty
    , hstreet1
    , hstreet2
    , hcity
    , hstate
    , hzip
    , Emp_DOB
    , Child_Abuse_Dt
    , Food_Handler_Exp_Dt
    , First_Aid_Exp_Dt
    , CPR_Exp_Dt
    , DMV_Record_Dt
    , Drivers_License_Exp_Dt
    , CBR_Comments
    , CBR_Exp_DT
    , fsa.Facility_ID
    , Director_Name
    , Site_Phone_No  FROM (
SELECT
      l.Staff_Training_ID
    , RANK() OVER ( PARTITION BY l.staff_training_id ORDER BY l.staff_training_id ) AS Rnk
    , s.Staff_ID
    , s.Staff_LName
    , s.Staff_MName
    , s.Staff_FName
    , s.hphone
    , s.Hcounty
    , s.hstreet1
    , s.hstreet2
    , s.hcity
    , s.hstate
    , s.hzip
    , s.Emp_DOB
    , l.Child_Abuse_Dt
    , l.Food_Handler_Exp_Dt
    , l.First_Aid_Exp_Dt
    , l.CPR_Exp_Dt
    , l.DMV_Record_Dt
    , l.Drivers_License_Exp_Dt
    , s.CBR_Comments
    , CBR_Exp_DT
    , fsa.Facility_ID
    , f.Director_Name
    , f.Site_Phone_No
FROM
      Staff_Training_Log AS l
INNER JOIN Staff AS s
      ON l.Staff_ID = s.Staff_ID
         AND l.Staff_ID = s.Staff_ID
INNER JOIN Facility_Staff_Assoc AS fsa
      ON l.Staff_ID = fsa.Staff_ID
INNER JOIN Facility AS f
      ON f.Facility_PK = fsa.Facility_ID
WHERE
      facility_ID = " & lv_Facility_ID & "
) as der WHERE rnk = 1

Open in new window

0
 
LVL 11

Expert Comment

by:yuching
ID: 37723533
Distinct will not work because the returned result contain multiple of the same rows i.e. from Staff_Training_Log for a staff, it can be lots of the records

Unless you want to select the latest tranining log or max for that staff only..i.e.

SELECT  l.Staff_Training_ID,
      s.Staff_ID, s.Staff_LName,
      s.Staff_MName, s.Staff_FName, s.hphone, s.Hcounty,
      s.hstreet1, s.hstreet2, s.hcity, s.hstate, s.hzip,
      s.Emp_DOB,
      l.Child_Abuse_Dt,
      l.Food_Handler_Exp_Dt,
      l.First_Aid_Exp_Dt,
      l.CPR_Exp_Dt,
      l.DMV_Record_Dt,
      l.Drivers_License_Exp_Dt,
      s.CBR_Comments, CBR_Exp_DT,
      fsa.Facility_ID, f.Director_Name, f.Site_Phone_No
FROM (
      
      SELECT      l.Staff_ID
            max(l.Staff_Training_ID) as Staff_Training_ID ,
            max(l.Child_Abuse_Dt) as Child_Abuse_Dt,
            max(l.Food_Handler_Exp_Dt) as Food_Handler_Exp_Dt,
            max(l.First_Aid_Exp_Dt) as First_Aid_Exp_Dt,
            max(l.CPR_Exp_Dt) as CPR_Exp_Dt,
            max(l.DMV_Record_Dt) as DMV_Record_Dt,
            max(l.Drivers_License_Exp_Dt) as Drivers_License_Exp_Dt,
            Staff_Training_Log l
      Group by l.Staff_ID
) l
INNER JOIN Staff AS s ON l.Staff_ID = s.Staff_ID
INNER JOIN Facility_Staff_Assoc AS fsa ON s.Staff_ID = fsa.Staff_ID
INNER JOIN Facility AS f ON f.Facility_PK = fsa.Facility_ID
WHERE facility_ID = lv_Facility_ID
ORDER BY s.Staff_LName
0
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 37724402
What's the idea behind
<<I get duplicates of a s.Staff_ID.  How can i remove duplicates of s.Staff_ID?>>

As previous poster explained Staff_id you will get duplicates when there are more than 1 records for that staff member in the tables you will join with (after filtering).
So if for that staf-member more records exists in table Staff_Training_Log of Facility_Staff_Assoc then you get multiple records.

So your query is not a list of staf members, but a list of staff members and their training and associated facility.  When both tables can have multiple records for your staff member then there will be a cartasian result between those 2 multiple related tables. 3 trainings and 2 facilities -> 6 rows for staf member.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 32

Expert Comment

by:awking00
ID: 37730283
Perhaps you can provide some sample relevant data for your tables and what you would like to see as results.
0
 

Author Comment

by:ocdc
ID: 37732086
One staff_ID can have multiple Facility_ID 's.  This is my problem.  See attached.
duplicateStaffId.JPG
0
 
LVL 32

Expert Comment

by:awking00
ID: 37733080
You are selecting far more attributes than your example shows, including a number of what appear to be dates. If you only want one row per staff_id, you need to determine the criteria for that selection, such as a latest date.
0
 

Author Closing Comment

by:ocdc
ID: 37755181
This was right.   Thanks.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 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