I have a requirement wherein I have 3 tables
我有一个要求,我有三个表
ContactInfo
ContactInfo
idContactinfo FirstName Lastname idCOmpanyid
---------------------------------------------------
1 Blah1 Blah1 1
2 Blah2 Blah2 1
3 Blah3 Blah3 1
4 Blah4 Blah4 1
ContactRole
ContactRole
idcontactrole IdContactRoleName idContactInfo
------------------------------------------------------
1 1 1
2 1 2
3 1 3
4 2 4
IdContactRoleLookup
IdContactRoleLookup
idcontactRole RoleName
----------------------------------
1 Admin
2 Secretary
I need a query that gives me a result like this
我需要一个查询来给出这样的结果
idcontactrolename Contacts
--------------------------------------------------------
Admin Blah1,Blah1;Blah2,Blah2,Blah3,Blah3
Secretary Blah4,Blah4
This is what I have till now !!
这就是我现在拥有的!!
SELECT l.sValue
, SUBSTRING((
SELECT C.sLast + ',' + C.sFirst + ';'
FROM contactinfo c
inner join contactrole crole
ON crole.idcontactinfo = c.idcontactinfo
WHERE crole.idcompany = '<<blah>>'
and c.idcompany = '<<blah>>'
FOR XML PATH('')
), 1, 1000000)
FROM CONTACTROLE CR
inner join contactinfo c on
cr.idcontactinfo = c.idcontactinfo
inner join lookupdata l on cr.idlookuprole = l.idlookupdata
where c.idcompany = '<<blah>>'
3 个解决方案
#1
1
I Would do somethng like this, where you use the ID of the main QUERY into your SUBQUERY
我将这样做,您将主查询的ID用于子查询
SELECT
l.sValue
, SUBSTRING((
SELECT C.sLast + ',' + C.sFirst + ';'
FROM contactinfo c
inner join contactrole crole
ON crole.idcontactinfo = c.idcontactinfo
WHERE c.idcompany = '<<blah>>'
AND c.idcontactrole = CR.idcontactrole
FOR XML PATH('')
), 1, 1000000)
FROM CONTACTROLE CR
inner join lookupdata l on cr.idlookuprole = l.idlookupdata
This will give you all the role for one Compagnie. If the role does'nt exist you will have a NULL value.
这会给你一个公司的所有角色。如果角色不存在,就会有一个空值。
#2
2
SELECT
RoleName,
STUFF(
(
SELECT ';' + b.FirstName + ', ' + b.LastName
FROM ContactRole a
INNER JOIN ContactInfo b
ON a.idContactInfo = b.idContactinfo
WHERE a.IdContactRoleName = r.idcontactRole
FOR XML PATH (''))
, 1, 1, '') AS NamesList
FROM IdContactRoleLookup r
GROUP BY idcontactRole, RoleName
- SQLFiddle Demo
- SQLFiddle演示
#3
1
You can use CROSS APPLY
to get the result:
你可以用CROSS APPLY得到结果:
select distinct l.RoleName,
left(list, len(list) -1) list
FROM CONTACTROLE CR
inner join IdContactRoleLookup l
on cr.IdContactRoleName = l.idcontactRole
cross apply
(
select C1.Lastname + ',' + C1.FirstName + ';'
from CONTACTROLE CR1
inner join contactinfo c1
on cr1.idContactInfo = c1.idContactinfo
where cr.IdContactRoleName = cr1.IdContactRoleName
FOR XML PATH('')
) t2 (list)
参见SQL小提琴演示
#1
1
I Would do somethng like this, where you use the ID of the main QUERY into your SUBQUERY
我将这样做,您将主查询的ID用于子查询
SELECT
l.sValue
, SUBSTRING((
SELECT C.sLast + ',' + C.sFirst + ';'
FROM contactinfo c
inner join contactrole crole
ON crole.idcontactinfo = c.idcontactinfo
WHERE c.idcompany = '<<blah>>'
AND c.idcontactrole = CR.idcontactrole
FOR XML PATH('')
), 1, 1000000)
FROM CONTACTROLE CR
inner join lookupdata l on cr.idlookuprole = l.idlookupdata
This will give you all the role for one Compagnie. If the role does'nt exist you will have a NULL value.
这会给你一个公司的所有角色。如果角色不存在,就会有一个空值。
#2
2
SELECT
RoleName,
STUFF(
(
SELECT ';' + b.FirstName + ', ' + b.LastName
FROM ContactRole a
INNER JOIN ContactInfo b
ON a.idContactInfo = b.idContactinfo
WHERE a.IdContactRoleName = r.idcontactRole
FOR XML PATH (''))
, 1, 1, '') AS NamesList
FROM IdContactRoleLookup r
GROUP BY idcontactRole, RoleName
- SQLFiddle Demo
- SQLFiddle演示
#3
1
You can use CROSS APPLY
to get the result:
你可以用CROSS APPLY得到结果:
select distinct l.RoleName,
left(list, len(list) -1) list
FROM CONTACTROLE CR
inner join IdContactRoleLookup l
on cr.IdContactRoleName = l.idcontactRole
cross apply
(
select C1.Lastname + ',' + C1.FirstName + ';'
from CONTACTROLE CR1
inner join contactinfo c1
on cr1.idContactInfo = c1.idContactinfo
where cr.IdContactRoleName = cr1.IdContactRoleName
FOR XML PATH('')
) t2 (list)
参见SQL小提琴演示