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

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')