Solved

sql select distinct statement

Posted on 2012-03-14
7
374 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

689 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