I have 3 tables with relationship is Policy (N) -> PolicyService <- Service (N):
DXBusinessPolicy_Policy
ID | Code | Name |
---|---|---|
1 | COMBO.2103001 | [Giá nền] T9/2020 #1 |
2 | IPTV-0121.002 | [Giá nền] T8/2020 #1 |
3 | INT.2103001 | Chính sách 2 |
DXBusinessPolicy_Service
ID | Code | Name |
---|---|---|
1 | INT | Internet |
2 | IPTV | IPTV |
DXBusinessPolicy_PolicyService
ID | PolicyID | ServiceID |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 2 | 2 |
4 | 3 | 1 |
The question: enter input service (ServiceCode), the output are PolicyID, PolicyCode, PolicyName and a list of services of that policy (string of list ServiceCode join with “,”).
For example: My input is: “INT”. Result expect:
PolicyCode | PolicyName | Services |
---|---|---|
COMBO.2103001 | [Giá nền] T9/2020 #1 | INT,IPTV |
INT.2103001 | Chính sách 2 | INT |
I tried to solve this question as follows:
ALTER PROC FindPolicyByService
@ServiceCode varchar(200)
AS
BEGIN
SELECT dbo.DXBusinessPolicy_Policy.ID AS PolicyID,
dbo.DXBusinessPolicy_Policy.Code AS PolicyCode,
dbo.DXBusinessPolicy_Policy.Name AS PolicyName,
STRING_AGG(dbo.DXBusinessPolicy_Service.Code, ',')
FROM dbo.DXBusinessPolicy_Policy
join dbo.DXBusinessPolicy_PolicyService ON dbo.DXBusinessPolicy_Policy.ID = dbo.DXBusinessPolicy_PolicyService.PolicyID
join dbo.DXBusinessPolicy_Service ON dbo.DXBusinessPolicy_PolicyService.ServiceID = dbo.DXBusinessPolicy_Service.ID
WHERE dbo.DXBusinessPolicy_Service.Code = @ServiceCode
GROUP by dbo.DXBusinessPolicy_Policy.ID, dbo.DXBusinessPolicy_Policy.Code, dbo.DXBusinessPolicy_Policy.Name
END
exec FindPolicyByService "INT"
But the result is not what I expected
PolicyCode | PolicyName | Services |
---|---|---|
COMBO.2103001 | [Giá nền] T9/2020 #1 | INT |
INT.2103001 | Chính sách 2 | INT |