I have 3 tables with relationship is Policy (N) -> PolicyService <- Service (N):

DXBusinessPolicy_Policy

DXBusinessPolicy_Service

DXBusinessPolicy_PolicyService

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:

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

CodePudding user response：

I finally found the most accurate answer to my question. Thanks all! The best solution is:

ALTER PROC FindPolicyByService
    @codes varchar(200)
AS 
BEGIN
   SELECT p.ID AS PolicyID,
       p.Code AS PolicyCode,
       p.Name AS PolicyName,
       STRING_AGG(s.Code, ',') AS ServiceCode
    FROM dbo.DXBusinessPolicy_Policy AS p
    JOIN dbo.DXBusinessPolicy_PolicyService AS ps ON p.ID = ps.PolicyID
    JOIN dbo.DXBusinessPolicy_Service AS s ON ps.ServiceID = s.ID
    WHERE p.ID IN 
    (
        SELECT subps.PolicyID
        FROM dbo.DXBusinessPolicy_PolicyService AS subps
        JOIN dbo.DXBusinessPolicy_Service AS subs ON subps.ServiceID = subs.ID
        WHERE subs.Code = @ServiceCode
    )
    GROUP by p.ID, p.Code, p.Name
END

Or the orther solution:

ALTER PROC FindPolicyByService
    @ServiceCode varchar(200)
AS 
BEGIN
    SELECT DIstinct policy.ID AS PolicyID,
           policy.Code AS PolicyCode,
           policy.Name AS PolicyName,
           (SELECT STRING_AGG(tempService.Code, ',')  FROM dbo.DXBusinessPolicy_Policy tempPolicy
                 JOIN dbo.DXBusinessPolicy_PolicyService tempPolicyService
                    ON tempPolicy.ID = tempPolicyService.PolicyID 
                 JOIN dbo.DXBusinessPolicy_Service tempService
                    ON tempPolicyService.ServiceID = tempService.ID
            WHERE policyservice.PolicyID = PolicyID) AS ServiceCode
    FROM dbo.DXBusinessPolicy_Policy policy
     JOIN dbo.DXBusinessPolicy_PolicyService policyservice
        ON policy.ID = policyservice.PolicyID 
     JOIN dbo.DXBusinessPolicy_Service service
        ON policyservice.ServiceID = service.ID AND service.Code = @ServiceCode
    GROUP BY
        policy.ID,
        policyservice.PolicyID,
        policy.Code,
        policy.Name
END

It all gave me the same result I expected:

CodePudding user response：

I tried to update my store like:

ALTER PROC FindPolicyByService
    @ServiceCode varchar(200)
AS 
BEGIN
    SELECT policy.ID AS PolicyID,
           policy.Code AS PolicyCode,
           policy.Name AS PolicyName,
           STRING_AGG(service.Code, ',') 
    FROM dbo.DXBusinessPolicy_Policy policy
    LEFT JOIN dbo.DXBusinessPolicy_PolicyService policyservice
        ON policy.ID = policyservice.PolicyID
    LEFT JOIN dbo.DXBusinessPolicy_Service service
        ON policyservice.ServiceID = service.ID AND
           service.Code = @ServiceCode
    GROUP BY
        policy.ID,
        policy.Code,
        policy.Name
END

exec FindPolicyByService "INT"

The result I got:

It looks like the service.Code = @codes condition is not work.

The result that I expected:

I also tried to do not use the service.Code = @codes condition

This result is close to my expectation, the rest I just want to get the services that include INT