我有一个包含多个关系表的sql数据库.主表中有一些字段多次引用另一个表.例如,假设我有一个销售人员的数据库,负责多个州的销售.我的数据库有State1,State2和State3的字段,所有这些字段都映射回State表.我无法弄清楚我的生活如何写一个查询来返回所有枚举状态的记录.如果我只需要一个国家领域,我知道我会做的：
SELECT Master.Name,State.Enumeration AS 'State' FROM MasterTable Master,StateTable State WHERE Master.State1 = State.ID;
我如何将其扩展到我所有的国家领域？
谢谢.
解决方法
从每个独特的连接返回一个列到状态：
select m.Name,s1.Enumeration as State1,s2.Enumeration as State2,s3.Enumeration as State3 from MasterTable m left join StateTable s1 on m.State1 = s1.ID left join StateTable s2 on m.State2 = s2.ID left join StateTable s3 on m.State3 = s3.ID
从3个连接中返回所有状态的1列：
select m.Name,ISNULL(s1.Enumeration + ',','')
+ ISNULL(s2.Enumeration + ','')
+ ISNULL(s3.Enumeration,'') as Enumeration
from MasterTable m
left join StateTable s1 on m.State1 = s1.ID
left join StateTable s2 on m.State2 = s2.ID
left join StateTable s3 on m.State3 = s3.ID
还有列查询…
select m.Name,ISNULL((select Enumeration from StateTable where ID = m.State1),'') as State1,ISNULL((select Enumeration from StateTable where ID = m.State2),'') as State2,ISNULL((select Enumeration from StateTable where ID = m.State3),'') as State3 from MasterTable m
