I have a many to many relationship between people and some electronic codes. The table with the codes has the code itself, and a text description of the code. A typical result set from a query might be (there are many codes that contain "broken" so I feel like it's better to search the text description rather than add a bunch of ORs for every code.)

id#    text of code
1234   broken laptop
1234   broken mouse

Currently the best way for me to get a result set like this is to use the LIKE%broken% filter. Without changing the text description, is there any way I can return only one instance of a code with broken? So in the example above the query would only return 1234 and broken mouse OR broken laptop. In this scenario it doesn't matter which is returned, all I'm looking for is the presence of "broken" in any of the text descriptions of that person's codes.

My solution at the moment is to create a view that would return

`id#    text of code
1234   broken laptop
1234   broken mouse`

and using SELECT DISTINCT ID# while querying the view to get only one instance of each.

EDIT ACTUALLY QUERY

SELECT tblVisits.kha_id, tblICD.descrip, min(tblICD.Descrip) as expr1 FROM tblVisits inner join icd_jxn on tblVisits.kha_id = icd_jxn.kha)id inner join tblICD.icd_fk=tblICD.ICD_ID group by tblVisits.kha_id, tblicd.descrip having (tblICD.descrip like n'%broken%')

• sql
• sql-server

You could use the below query to SELECT the MIN code. This will ensure only text per id.

SELECT t.id, MIN(t.textofcode) as textofcode
FROM table t
WHERE t.textofcode LIKE '%broken%'
GROUP BY t.id

Updated Actual Query:

SELECT tblVisits.kha_id, 
  MIN(tblICD.Descrip) 
FROM tblVisits 
INNER JOIN icd_jxn ON tblVisits.kha_id = icd_jxn.kha)id 
INNER JOIN tblicd ON icd_jxn.icd_fk = tbl.icd_id
WHERE tblICD.descrip like n'%broken%'
GROUP BY tblVisits.kha_id