根据列中的值重新处理来自不同表的数据

Please pardon me if this question has been asked before, but I simply don't have enough vocabulary to search for what I need as a novice in data bases.

如果之前已经问过这个问题,请原谅我,但我根本没有足够的词汇来搜索我在数据库中作为新手所需要的东西。

I am using SQL server 2008. I have a table tblPDCDetails with several columns. One of the columns PDCof holds values :

我正在使用SQL Server 2008.我有一个包含多个列的表tblPDCDetails。其中一列PDCof包含值:

"A"(for applicant), 
"C" for coapplicant, 
"G" (for Guarantor).

Another column HolderID holds uniqueid (of holder). The PDCHolders reside in their respective tables: Applicants in tblApplBasicDetails, CoApllicants in their own table and so on.

另一列HolderID拥有uniqueid(持有者)。 PDCHolders驻留在各自的表中:tblApplBasicDetails中的申请人,他们自己的表中的CoApllicants等等。

Now what I need is how should I retrive the names of holders from their respective tables, depending on the value in PDCof column.

现在我需要的是如何从各自的表中检索持有者的名称,具体取决于PDCof列中的值。

Can I do it at all? If no how should I work around this?

我可以这样做吗?如果没有,我应该如何解决这个问题?

2 个解决方案

#1

This should do:

这应该做:

SELECT  A.*,
        COALESCE(B.Name,C.Name,D.Name) Name
FROM dbo.tblPDCDetails A
LEFT JOIN dbo.tblApplBasicDetails B
    ON A.HolderID = B.HolderID
    AND A.PDCof = 'A'
LEFT JOIN dbo.tblCoApplBasicDetails C
    ON A.HolderID = C.HolderID
    AND A.PDCof = 'C'
LEFT JOIN dbo.tblGuarantorlBasicDetails D
    ON A.HolderID = D.HolderID
    AND A.PDCof = 'G'

#2

The other option is to use a case switch:

另一种选择是使用案例开关:

Select case Main.PDCof
    when 'A' then (select HolderID from Applicants where main.value = value)
    when 'C' then (select HolderID from CoApplicants where main.value = value)
    when 'G' then (select HolderID from Guarantor where main.value = value)
    end
    ,main.*
from tblPDCDetails main

Depends on whether you run this a few times a day, or a few thousand times an hour

取决于你是每天运行几次,还是每小时运行几千次

#1