多表关联问题

[飞驴]

table1
id name
1 名称

table2
id name table1id
1 m1 1
2 m2 1
3 m3 1
4 m4 1

希望结果
名称
m1
m2
m3
m4

[AcHerat]

select name from table2 where table1id in (select id from table1)

[开着拖拉机泡妞]

select name from table2 
where table1id in (select id from table1)

[trunjun]

不知所云！！！！！！！！

[飞驴]

iTarget_BrandOption表
BrandID BrandName
1 Bud

iTarget_PackageOption表
PackageID PackageName BrandID
1 BBT 1
2 SBT 1
3 CAN 1
4 OTH 1

想要的结果是
Bud
BBT
SBT
CAN
OTH

[开着拖拉机泡妞]

描述一下，BrandName跟PackageName什么关系

[飞驴]

[Quote=引用 5 楼 travylee 的回复:]

描述一下，BrandName跟PackageName什么关系
[/Quote]
iTarget_PackageOption表的BrandID就是iTarget_BrandOption表的BrandID，两个表关联

[开着拖拉机泡妞]

[Quote=引用 6 楼 ljx85249201 的回复:]

引用 5 楼 travylee 的回复:

描述一下，BrandName跟PackageName什么关系

iTarget_PackageOption表的BrandID就是iTarget_BrandOption表的BrandID，两个表关联
[/Quote]

我的意思是

Bud
BBT
SBT
CAN
OTH
BUD跟下面的有什么关系

[飞驴]

bud就是第一个表中的那个
BrandID BrandName
1 Bud
另外一个表示用ID关联

[开着拖拉机泡妞]

--> 测试数据：[iTarget_BrandOption]
go
if object_id('[iTarget_BrandOption]') is not null
drop table [iTarget_BrandOption]
go
create table [iTarget_BrandOption](
[BrandID] int,
[BrandName] varchar(3)
)
go
insert [iTarget_BrandOption]
select 1,'Bud'


--> 测试数据：[iTarget_PackageOption]
go
if object_id('[iTarget_PackageOption]') is not null
drop table [iTarget_PackageOption]
create table [iTarget_PackageOption](
[PackageID] int,
[PackageName] varchar(3),
[BrandID] int
)
go
insert [iTarget_PackageOption]
select 1,'BBT',1 union all
select 2,'SBT',1 union all
select 3,'CAN',1 union all
select 4,'OTH',1


;with t
as(
select
a.BrandName as name1,b.PackageName as name2
from [iTarget_BrandOption] a
inner join [iTarget_PackageOption] b
on a.BrandID=b.BrandID
)

select distinct name1 from t
union all
select name2 from t
where name1 in(select name1 from t a where t.name1=a.name1)

/*
name1
Bud
BBT
SBT
CAN
OTH
*/

[flyfly2008]

不是用Union all 连接一下就可以啦！