Select unique/distinct for singular column?

Here is the SQL query I'm working on:

SELECT [CaseInfo].[CaseID], LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney, [Insured].[LastName] + ' v. ' + [Claimant].[LastName] AS CaseTitle, [CaseInfo].[CaseID]
FROM [CaseInfo], Attorney, Insured, Claimant, Insurer
WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)

There are multiple claimants and insured parties for each CaseID.  I want to return only ONE result per each unique CaseID.  How can I do this?  I've experiemented with Distinct and Unique to no avail, but I'm new at SQL so I'm sure I just missed something.
brassmonkeyboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
when you have multiple claimants and insured parties related to a caseID which one do you want to display?
may be you have to use group by and get min or max from claimants and insured.
it helps if you can give some sample data and the results you are looking for.

try this and see

SELECT [CaseInfo].[CaseID], Max( LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1)) AS Attorney,
Max([Insured].[LastName] + ' v. ' + [Claimant].[LastName]) AS CaseTitle, [CaseInfo].[CaseID]
FROM [CaseInfo], Attorney, Insured, Claimant, Insurer
WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)
group by [CaseInfo].[CaseID]

0
imran_fastCommented:

SELECT       [CaseInfo].[CaseID],
      LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney,
      ( select top 1 lastname  from Insurer where [Insurer].[InsurerID] = [Caseinfo].[InsurerID] order by lastname) + ' v. '
      + ( select top 1 lastname  from Claimant where [Claimant].[CaseID] = [Caseinfo].[CaseID] order by lastname) AS CaseTitle,
      [CaseInfo].[CaseID]
FROM
      [CaseInfo]
      inner join  Attorney
      on [Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]
      inner join  Insured
      on [Insured].[CaseID] = [Caseinfo].[CaseID]
      
WHERE


       [Caseinfo].CaseID = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
i find your design confusing... shouldn't the claimant also have relations with Insurers...
and indeed many insueres could be involved on both sides dependant on policy coverage / periods of indemnity...
please confirm the relation ships between caseinfo and the other tables...

i'd also expect some indicator for the principal opponnents to be made somewhere...

hth

SELECT C.[CaseID]
      , LEFT(A.[FirstName],1) + LEFT(A.[MiddleName],1) + LEFT(A.[LastName],1) AS Attorney
      , I.[LastName] + ' v. ' + Clm.[LastName] AS CaseTitle
      , C.[CaseID]
 FROM [CaseInfo] as C
inner Join Attorney as A
   on A.[AttorneyID] = C.[HeadAttorneyID]
Inner Join Insured as I      
   on I.[CaseID] = C.[CaseID])
Inner Join Insurer as Ins
   on Ins.InsurerID = C.InsurerID
Inner Join (select caseid,min(lastname) as lastname from Claimant group by caseid)  as Clm
   on Clm.[CaseID] = C.[CaseID]

WHERE C.CaseID = 1

 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brassmonkeyboyAuthor Commented:
I honestly don't care which of the names to use.  I just need one from each.

The insurer is used in different forms of the query (this is being generated in VB.NET, and the query is different based on the data input.)  So it's only there because it's needed SOMETIMES.

Imran fast, I used a form of what you gave me ("SELECT TOP 1"), but it seems that I have to include every single field in the SELECT statement in the Group By list.  Is this true?

Here's my query:

SELECT [CaseInfo].[CaseID], LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney, (SELECT TOP 1 [Insured].[LastName] FROM Insured WHERE Insured.CaseID = CaseInfo.CaseID) + ' v. ' + (SELECT TOP 1 [Claimant].[LastName] FROM Claimant WHERE Claimant.CaseID = CaseInfo.CaseID) AS CaseTitle, [CaseInfo].[CaseID]

FROM [CaseInfo], Attorney, Insured, Claimant, Insurer

WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)

GROUP BY CaseInfo.CaseID
0
brassmonkeyboyAuthor Commented:
I just added all the select fields into the group by and then changed "SELECT" to "SELECT DISTINCT" and it worked!

SELECT DISTINCT [CaseInfo].[CaseID], LEFT([Attorney].[FirstName],1) + LEFT([Attorney].[MiddleName],1) + LEFT([Attorney].[LastName],1) AS Attorney, (SELECT TOP 1 [Insured].[LastName] FROM Insured WHERE Insured.CaseID = CaseInfo.CaseID) + ' v. ' + (SELECT TOP 1 [Claimant].[LastName] FROM Claimant WHERE Claimant.CaseID = CaseInfo.CaseID) AS CaseTitle, [CaseInfo].[CaseID]

FROM [CaseInfo], Attorney, Insured, Claimant, Insurer

WHERE (([Attorney].[AttorneyID] = [Caseinfo].[HeadAttorneyID]) AND ([Insured].[CaseID] = [Caseinfo].[CaseID]) AND ([Claimant].[CaseID] = [Caseinfo].[CaseID]) AND ([Insurer].InsurerID = [Caseinfo].InsurerID) AND [Caseinfo].CaseID = 1)

GROUP BY CaseInfo.CaseID, Attorney.FirstName, Attorney.MiddleName, Attorney.LastName, Insured.LastName, Claimant.LastName, Insured.CaseID, Claimant.CaseID
0
appariCommented:

its confusing, your final sql looks like the sql i posted and you accepted other answer as solution
0
brassmonkeyboyAuthor Commented:
The only reason my query looks like what you posted is because your suggestion was a modification of the original query <i>I</i> posted.  None of the changes you made were used because they didn't work.  The methods suggested in the awardee's suggestion were used with success.  Hence, he is awarded the points.

0
appariCommented:

No problem,
in the answer accepted and in the actual query in your question there is no group by and the final query you posted is using group by clause as suggested in my query, that cause the confusion
0
brassmonkeyboyAuthor Commented:
Gotcha.  THe GROUP BY was added because of an error that showed up.  The part that ehlped me was the (SELECT TOP 1).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.