Solved

sql select distinct statement

Posted on 2012-03-14
7
368 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
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 500 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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