Solved

sql select distinct statement

Posted on 2012-03-14
7
364 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
One staff_ID can have multiple Facility_ID 's.  This is my problem.  See attached.
duplicateStaffId.JPG
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
This was right.   Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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.‚Äč
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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

14 Experts available now in Live!

Get 1:1 Help Now