Dillemma regarding Distinct and Join

[MayoorPatel]

Hi there I have a query which is used to search a database for judgements. Each judgements can have many Categories and each category can have many subcategories.

select  j.id, sc.[description] as subcategory, sc.[id] as subcatid, c.[description] as category, c.num as catid,	
	j.file_no_1 + ' ' + j.file_no_2 + ' ' + j.file_no_3  as 'filenumber'
from judgment j
left join subcategoryjudgementmap sjm on j.id = sjm.judgement_id
left  join subcategory sc on sjm.subcategory_id = sc.id
left join category c on sc.parent_num = c.num
where j.[id] = @DecisionId

However I only want distinct judgements without losing the join. Can anyone show me how to amend my query so that it only pulls off distinct judgements.

 

[r937]

you only want distinct judgements, and yet a judgement can have many categories and each category can have many subcategories?

in that case, you will have to specify which category/subcategory you want to see for each judgement -- the earliest? the one with the lowest collating description?


r937.com | rudy.ca