请帮忙看一下下列sql语句有什么错误谢谢
a表中有如下几条记录
MC             BH
jc/m         1
kv/s         2
tm/ty       3
rt/b         4
ts/m         5

b表中字段
bh     name
  1       M
  3       TS

我想查询a表中的MC中   不包有   b表中name字段下的记录

SELECT   *
FROM   a   CROSS   JOIN
            b
WHERE   (a.mc   not     LIKE   '% '   +   name   +   '% ')    
这样不能查询出所想要的结果

------解决方案--------------------
select a.* from a,b where a.BH = b.bh and charindex(b.name,a.MC)> 0

------解决方案--------------------
select * from a where mc not in (select name from b)
------解决方案--------------------
drop table a,b
go
create table a(MC varchar(10),BH int)
insert into a
select 'jc/m ',1
union all select 'kv/s ',2
union all select 'tm/ty ',3
union all select 'rt/b ',4
union all select 'ts/m ',5
create table b(bh int,name varchar(10))
insert into b
select 1, 'M '
union all select 3, 'TS '

select * from a
where not exists(select 1 from b where charindex(b.name,a.mc)> 0)
/*
MC BH
---------- -----------
kv/s 2
rt/b 4

（所影响的行数为 2 行）
*/
------解决方案--------------------
select MC ,BH
from a
where left(MC,charindex( '/ ',MC)-1) not in (select name from b)
and right(MC,charindex( '/ ',reverse(MC))-1) not in (select name from b)
------解决方案--------------------
Create Table a
(MC Varchar(10),
BH Int)
Insert a Select 'jc/m ', 1
Union All Select 'kv/s ', 2
Union All Select 'tm/ty ', 3
Union All Select 'rt/b ', 4
Union All Select 'ts/m ', 5

Create Table b
(bh Int,
name Varchar(10))
Insert b Select 1, 'M '
Union All Select 3, 'TS '
GO
SELECT a.* FROM a
Where NOT Exists(Select name From b Where '/ ' + a.mc + '/ ' Like '%/ ' + name + '/% ')

SELECT a.* FROM a
Where NOT Exists(Select name From b Where CharIndex( '/ ' + name + '/ ', '/ ' + a.mc + '/ ') > 0)
GO
Drop Table a, b
/*
MC BH
kv/s 2
tm/ty 3
rt/b 4
*/