Query bring back multiple values I only want one

[jeffwest21]

I have the following code in a query

SELECT Distinct p.ID,p.Forename, p.Surname,p.[Event Code],convert(varchar(10),[Event Date],103) AS Event_Date,convert(varchar(10),c.[Contact Date],103) as Contact_Date,
convert(varchar(10),c.[Callback date],103) AS Callback_Date,p.[Date of Birth],p.Future_Calling,p.Being_Updated,  
CASE When Future_Calling IS NULL then Call_Number_Int 
when Future_Calling = 'int' Then Call_Number_Int 
when Future_Calling = '2A' then Call_Number_2A  
when Future_Calling = '2B' then Call_Number_2B 
when Future_Calling = '3A' then Call_Number_3A when Future_Calling = '3B' then Call_Number_3B end AS Calls,cc.Category AS [Careers Category],
CASE When Future_Calling IS NULL then O.outcome
when Future_Calling = 'int' Then O.outcome 
when (Future_Calling = '2A' and Call_number_2A is NULL) then O.outcome 
when (Future_Calling = '2A' and Call_number_2A is not NULL) then OFP.outcome
when (Future_Calling = '2B' and Call_number_2B is NULL) then O.outcome 
when (Future_Calling = '2B' and Call_number_2B IS NOT NULL) then OFP.outcome
when (Future_Calling = '3A' and Call_number_3A is NULL) then O.outcome 
when (Future_Calling = '3A' and Call_number_3A is not NULL) then OFP.outcome    
when (Future_Calling = '3B' and Call_number_3B is NULL) then O.outcome 
when (Future_Calling = '3B' and Call_number_3B is not NULL) then OFP.outcome    
end as [Outcome]
FROM dbo.contact c,dbo.Prospect p,dbo.Career_Category cc,Outcome o,Outcome_FUP OFP
where p.id = c.[prospect id]
and [Closed Date] is null
and p.[Career Category] = cc.Id 
and c.[ID] IN ( SELECT max(d.ID)  
FROM dbo.contact d GROUP BY d.[Prospect ID])
and C.Outcome = case when Future_Calling = 'int' Then o.id else ofp.id end
order by surname

It is supposed to bring back just one entry for each row in the database, however, it's fine if there is only something in the Call_Number_int column, otherwise it brings back multiple values, i have been hitting my head against a wall trying to work out what is wrong, any chance someone can spot it for me.

It reads the data from a series of linked SQL tables.

'Clever boy...'

 

[BigRed1212]

I would try to simplify things down.

Kill most of the stuff out of the query you posted- keep just enough to see the problem.

If you really want help, explain your data structure other than just "linked tables", provide some example data, show the current results, and show the desired results. That's probably why no one has volunteered anythign so far.

Wouldn't hurt to write in ANSI SQL either.