The closest I can come up with is something like this.
select *
from security_assignment sa
join matter m on m.r_type = sa.r_type
join [group] g on g.group_id = sa.group_id
join category c on c.category_id = m.category_id and m.r_type = sa.r_type
This returns both 61 and 494 for all the values that match but obviously is not totally correct as the matter gets mixed into it. Unless you can explain this in a way that makes sense I am going to leave this to somebody else.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/