Honestly, you should first be looking at normalising the table design. The first is not normalised and hence you have these kinds of problems.
To solve it in the mean time, get the function DelimitedSplit8k (it's from an article on this site, google will find it) and use that to split the comma-delimited list up. You can then join with a LIKE predicate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass