如何根据某一表中字段的值确定选择与哪一个表进行联合查询

[叮咚叮咚叮叮咚]

hy_CustomItem表存放某种类型会员自己定义的主页上面显示的栏目,addBy是会员id，memberType是会员类型，可能值为"lawyer"\ "office"\ "public"等,当memberType="lawyer"时与hy_lawyer表联合查询获取lawyerName,为office时与hy_office查询取得officeName....

hy_lawyer表存放律师会员记录
hy_office表存放律所会员记录

如果我只是想获得lawyerName的话可以通过
select itemname,lawyerName from hy_CustomItem,hy_lawyer where memberType='lawyer' and addBy=lawyerid
但是现在我想根据memberType选择与哪一个表进行联合查询并取得lawyerName 或者officename，不知道应该如何操作？


以下为建表脚本

CREATE TABLE [Hy_Lawyer] (
[lawyerID] [int] IDENTITY (1, 1) NOT NULL ,
[lawyerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Hy_office] (
[office_ID] [int] IDENTITY (1, 1) NOT NULL ,
[officeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [hy_CustomItem] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[itemName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[addBy] [int] NULL ,
[memberType] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_hy_CustomItem] PRIMARY KEY CLUSTERED
(
[itemID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into hy_lawyer values('王强')
insert into hy_office values('上海新锐律师事务所')

insert into hy_customItem values('成功案例',1,'lawyer')
insert into hy_customItem values('成功案例',1,'office')

[moonshineidolon]

select itemname,lawyerName from hy_CustomItem,hy_lawyer where memberType='lawyer' and addBy=lawyerid
union all
select itemname,officeNamefrom hy_CustomItem,hy_office where memberType='office' and addBy=office_ID

[叮咚叮咚叮叮咚]

楼上的理解错了。
比如现在我想取得itemid=1 的添加者该如何获得

[moonshineidolon]

select * from hy_customItem A inner join (
select lawyerID as addBy,lawyerName,'lawyer' memberType from hy_lawyer
union all
select office_ID as addBy ,officeName ,'office' memberType from Hy_office
) B
on a.addBy = b.addby and a.memberType = b.memberType

where a.itemid =1

[moonshineidolon]

在查询分析器中执行：

CREATE TABLE [Hy_Lawyer] (
[lawyerID] [int] IDENTITY (1, 1) NOT NULL ,
[lawyerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Hy_office] (
[office_ID] [int] IDENTITY (1, 1) NOT NULL ,
[officeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_office] PRIMARY KEY NONCLUSTERED
(
[office_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [hy_CustomItem] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[itemName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[addBy] [int] NULL ,
[memberType] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_hy_CustomItem] PRIMARY KEY CLUSTERED
(
[itemID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into hy_lawyer values('王强')
insert into hy_office values('上海新锐律师事务所')

insert into hy_customItem values('成功案例',1,'lawyer')
insert into hy_customItem values('成功案例',1,'office')

select * from hy_customItem


select * from hy_customItem A inner join (
select lawyerID as addBy,lawyerName,'lawyer' memberType from hy_lawyer
union all
select office_ID as addBy ,officeName ,'office' memberType from Hy_office
) B
on a.addBy = b.addby and a.memberType = b.memberType

where a.itemid =1

[叮咚叮咚叮叮咚]

up
期待高人出现