在SQL Server中搜索表两次

Question

我已经遇到这个问题好几次了，我不确定如何解决它。我有一个查询，它需要访问同一个表两次才能挑选出两个不同的人:顾问和客户联系人。

顾问+客户查询如下所示

select 
    CRM7.contact.contact_id as CustomerID, 
    CRM7.contact.name + ' ' + CRM7.person.firstname + ' ' + CRM7.person.LASTNAME as CustomerName, 
    CRM7.person.firstname + ' ' + CRM7.person.lastname as ConsultantName,
    CRM7.udcontactsmall.long08 as WriteLic, 
    CRM7.udcontactsmall.long17 as ReadLic, 
    CRM7.udcontactsmall.long09 as HasMaint, 
    CRM7.udlist.name as BCVer 
from 
    CRM7.contact,
    CRM7.udcontactsmall 
left join 
    CRM7.associate on CRM7.udcontactsmall.long11 = CRM7.associate.associate_id 
left join 
    CRM7.person on CRM7.associate.person_id = CRM7.person.person_id 
left join 
    CRM7.udlist on CRM7.udcontactsmall.long07 = CRM7.udlist.UDList_id 
where 
    CRM7.contact.category_idx = '2' and
    CRM7.contact.userdef_id = CRM7.udcontactsmall.udcontactsmall_id
order by 
    CRM7.contact.name

SELECT  
    C.NAME+' Kontakt '+ P.FIRSTNAME+' '+P.LASTNAME AS CONTACT
FROM 
    CRM7.PERSON P
LEFT OUTER JOIN 
    CRM7.CONTACT C ON P.CONTACT_ID = C.CONTACT_ID
WHERE 
    C.CATEGORY_IDX IN ('2','5') 
    AND P.RETIRED = 0  
ORDER BY 
    C.NAME, P.LASTNAME, P.FIRSTNAME

数据集：

http://oi61.tinypic.com/2j66cjq.jpg

如何获得同时返回客户顾问和客户联系人的查询？

Answer 1

这应该可以满足您的目的：

select 
    CRM7.contact.contact_id as CustomerID
    ,CRM7.contact.name+' '+CRM7.person.firstname+' '+CRM7.person.LASTNAME as CustomerName
    ,CRM7.person.firstname+' '+CRM7.person.lastname as ConsultantName
    ,CRM7.udcontactsmall.long08 as WriteLic
    ,CRM7.udcontactsmall.long17 as ReadLic
    ,CRM7.udcontactsmall.long09 as HasMaint
    ,CRM7.udlist.name as BCVer 
    ,C.NAME+' Kontakt '+ CRM7.person.FIRSTNAME+' '+ CRM7.person.LASTNAME AS CONTACT
from 
    CRM7.contact
    ,CRM7.udcontactsmall 
    left join CRM7.associate on CRM7.udcontactsmall.long11=CRM7.associate.associate_id 
    left join CRM7.person on CRM7.associate.person_id=CRM7.person.person_id 
    left join CRM7.udlist on CRM7.udcontactsmall.long07=CRM7.udlist.UDList_id 
    LEFT OUTER JOIN (SELECT CONTACT_ID,NAME FROM CRM7.CONTACT WHERE CATEGORY_IDX in ('2','5') AND P.RETIRED=0) C ON CRM7.person.CONTACT_ID=C.CONTACT_ID
where 
    CRM7.contact.category_idx='2' 
    and CRM7.contact.userdef_id=CRM7.udcontactsmall.udcontactsmall_id 
order by 
    CRM7.contact.name

我对第二个查询中的contact表使用了连接，并将其添加到第一个查询中，以获取客户联系人：

LEFT OUTER JOIN 
    (SELECT CONTACT_ID, NAME 
     FROM CRM7.CONTACT 
     WHERE CATEGORY_IDX IN ('2','5') AND P.RETIRED = 0) C ON CRM7.person.CONTACT_ID = C.CONTACT_ID