?
Solved

sql select distinct statement

Posted on 2012-03-14
7
Medium Priority
?
377 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 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