量表联合的问题
有两个表如下：
a表
idname1
1
2牌
3箱
4
b表
idname2
1501
2502
3503
4袋
5箱
希望可以达成如下效果：
idname1name2
1501
1502
1503
4袋
4箱
也就是说，假如a表字段name1包含在b表字段name2中，就联合在一起。
请不吝赐教，在线等，多谢了！
[解决办法]
3应该也复合的吧？

----------------------------------------------------------------
-- Author  :DBA_Huangzj()
-- Date    :2014-01-21 17:16:18
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--Dec 28 2012 20:23:12 
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([id] int,[name1] varchar(4))
insert [a]
select 1,'' union all
select 2,'牌' union all
select 3,'箱' union all
select 4,''
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([id] int,[name2] varchar(8))
insert [b]
select 1,'501' union all
select 2,'502' union all
select 3,'503' union all
select 4,'袋' union all
select 5,'箱'
--------------开始查询--------------------------


select a.id,a.name1,b.name2
 from [a] full JOIN [b] ON CHARINDEX(name1,name2,1)>0
 WHERE b.id IS NOT NULL 
 ORDER BY a.id
----------------结果----------------------------
/* 
id          name1 name2
----------- ----- --------
1                501
1                502
1                503
3           箱    箱
4               箱
4               袋

*/

create table a(id int,name1 varchar(20))

insert into a
select 1,'' union all
select 2,'牌' union all
select 3,'箱' union all
select 4,''

create table b(id int,name2 varchar(20))

insert into b
select 1,'501' union all
select 2,'502' union all
select 3,'503' union all
select 4,'袋' union all
select 5,'箱'
go

select a.id,a.name1,b.name2
from b
inner join a
        on b.name2 like '%'+a.name1
/*
idname1name2
1501
1502
1503
4袋
4箱
*/