Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
salthead
Starting Member
8 Posts |
 Posted - 2008-06-06 : 16:11:11
|
| I need to see whether there's any unapproved timecards, and if so compile a list of supervisors that need to make approvals.my original statement was:SELECT DISTINCT EmployeeMaster.SupervisorNumber , SupervisorMaster.EmailAddress , SupervisorMaster.EmployeeMasterNumber , RTRIM(SupervisorMaster.FirstName) + ' ' + RTRIM(SupervisorMaster.LastName) AS SupervisorNameFROM Cards LEFT OUTER JOIN EmployeeMaster ON Cards.EmployeeNumber = EmployeeMaster.EmployeeNumber LEFT OUTER JOIN SupervisorMaster ON EmployeeMaster.SupervisorNumber = SupervisorMaster.EmployeeNumberWHERE (Cards.SupApp = 0)And this worked great until they asked for a date range.If I am correct the distinct will look for the first occurrence that meets the select criteria and ignore the rest of the rows that meet the criteria. Leading to the possiblity that the first row is not a timecard that meets the date requirement, and even though other timecards exist that will satisfy the condition, they will not be tested because of the Distinct clause.What's the best way to handle a situation like this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-06 : 16:36:47
|
| DISTINCT just removes duplicates from your result set. But the duplicates would be for the columns returned in your result set only.DISTINCT has no effect on the filtering in the WHERE clause. It only affects what is outputted.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
salthead
Starting Member
8 Posts |
Posted - 2008-06-06 : 18:28:47
|
| Thanks Tara,I obviously misunderstood what DISTINCT was doing for me. |
|
|
|
|
|
|
It seemed like a simple SQL statement