Trying to return 1 record for each row

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trying to return 1 record for each row

Author

Topic

chyter
Starting Member

1 Post

Posted - 2009-06-08 : 14:06:56

First of all my company database structure sucks. I am trying to return data from 8 different tables, but even using DISTINCT I am still getting muliple records which I do not want. This query works until I add the CompletionDate table and thats when it start to show the muliples. Can someone please help my co-worker are not willing. Here is the query I am using.

SELECT DISTINCT
CaseData.StateCode,
CaseTypes.Description,
CaseData.CaseDataUId,
CaseData.ClientRefNumber,
CaseData.ClientNumber,
Clients.Name + ' | ' + CaseData.CaseShortName AS Name,
Users.LastName AS Attorney,
Counties.CountyName,
Judges.LastName AS Judge,
CaseData.OpenDate,
CaseData_Steps.CompletionDate,
CaseTypes_Drivers.StepName
FROM CaseTypes
INNER JOIN CaseData ON CaseTypes.CaseTypeUId = CaseData.FCCaseTypeUId
INNER JOIN Clients ON CaseData.ClientNumber = Clients.ClientNumber
INNER JOIN Users ON CaseData.AttorneyUId = Users.UserUId
INNER JOIN Counties ON CaseData.CountyUId = Counties.CountyUId
LEFT OUTER JOIN CaseTypes_Drivers
INNER JOIN CaseData_Steps ON CaseTypes_Drivers.CaseTypeUId = CaseData_Steps.CaseTypeUId ON CaseData.CaseDataUId = CaseData_Steps.CaseDataUId
LEFT OUTER JOIN Judges ON CaseData.JudgeUId = Judges.JudgeUId
WHERE
(CaseData.FCCaseTypeUId IN (5, 20))
AND (CaseData.CaseStatus = 1)
AND (CaseData.DefaultType = 'FC')
ORDER BY
CaseData_Steps.CompletionDate DESC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-08 : 14:35:25

When a table is introduced into a query which results in multiple rows (per original query row) that means that that table has a many-to-one relationship with the table(s) it is correlated with. In that case you will need to do one of a couple things. Either use GROUP BY for the columns that are not duplicated and then aggregate the new columns (ie: max, sum, avg, etc). Or alternatively you could CROSS APPLY a derived correlated table where you select just one of the possible rows preferenced by some critieria (ie: most recent row, first row alphabetically based on one of the columns, etc)

EDIT:
I suspect you will need more specific help. If that is the case follow the instructions in this link to pose a question that we can answer:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

EDIT (again):
as a final suggestion, be nicer to your co-workers so they are willing to help you

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources