0

I have a table that contains references to clients impacted in a problem. The parent table contains the problem info, specifically the ProblemID (PbMID). Since one problem can affect multiple clients, we store the client impacted data in a child table. The child table contains an ID field for housekeeping, a PbMID field which foreign keys back to the parent table, and a Company field containing the text name of the client. I have a requirement to pull all the problems were a SINGLE client was impacted. If I use DISTINCT, I get all single client rows, but I also get the FIRST row of a multi-client problem, which is not what I'm being asked for.

Here's the client impacted table example

ID  | PbMID | Company    | 
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
4   | 22    | Company 1  | Invalid
5   | 22    | Company 4  | Invalid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
8   | 25    | Company 1  | Invalid
9   | 25    | Company 8  | Invalid
10  | 25    | Company 10 | Invalid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

The rows marked INVALID would not be included, since they reflect multi-client problems.

So, ideally, the return would be:

ID  | PbMID | Company    | 
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

Any help would be greatly appreciated. SQL isn't my forte, so I've been trying to wrap my head around this with no luck.

1
2

You could use GROUP BY, HAVING and a Common Table Expression (CTE) to obtain the data.

The GROUP BY and HAVING provides all those PbMIDs that only impacted a single company. If you need PbMIDs that impacted n companies you could change the HAVING to HAVING =n where n is the required number of companies.

SELECT
    PbMID
FROM
    ChildTable
GROUP BY PbMId
HAVING COUNT(Company) =1; 

This can then be combined with a CTE to produce the final query below.

WITH CTE_SingleInstance (PbMIDSinglInstance)
AS
(
    SELECT
        PbMID
    FROM
        ChildTable
    GROUP BY PbMId
    HAVING COUNT(Company) =1    
)
SELECT
    ChildTable.ID,
    ChildTable.PbMId ,
    ChildTable.Company
FROM
    ChildTable CT
JOIN
    CTE_SingleInstance  CTES
ON
    CT.PbMId  = CTES.PbMIDSinglInstance;
0

I believe this gets you what you're after ;-)

WITH ProblemTable AS (
SELECT 1  AS ID, 1   AS PbMID,'Company 1 ' AS Company
UNION ALL SELECT 2  AS ID, 4   AS PbMID,'Company 2 ' AS Company
UNION ALL SELECT 3  AS ID, 6   AS PbMID,'Company 3 ' AS Company
UNION ALL SELECT 4  AS ID, 22  AS PbMID,'Company 1 ' AS Company
UNION ALL SELECT 5  AS ID, 22  AS PbMID,'Company 4 ' AS Company
UNION ALL SELECT 6  AS ID, 23  AS PbMID,'Company 5 ' AS Company
UNION ALL SELECT 7  AS ID, 24  AS PbMID,'Company 6 ' AS Company
UNION ALL SELECT 8  AS ID, 25  AS PbMID,'Company 1 ' AS Company
UNION ALL SELECT 9  AS ID, 25  AS PbMID,'Company 8 ' AS Company
UNION ALL SELECT 10 AS ID, 25  AS PbMID,'Company 10' AS Company
UNION ALL SELECT 11 AS ID, 26  AS PbMID,'Company 2 ' AS Company
UNION ALL SELECT 12 AS ID, 27  AS PbMID,'Company 4 ' AS Company
)

SELECT  p.ID
        ,p.PbMID
        ,p.Company
FROM    ProblemTable p
        CROSS APPLY (
                        SELECT  COUNT(1) AS ProblemCount
                        FROM    ProblemTable p2
                        WHERE   p.PbMID = p2.PbMID
                    ) pc
WHERE   1 = 1
        AND pc.ProblemCount = 1
0

A simple subquery that counts the the number problemsids gives yu the correct answer

SELECT 
[ID], [PbMID], [Company]
FROM
Table1
WHERE [PbMID] IN ( SELECT  [PbMID] 
                   FROM table1 
                  GROUP BY [PbMID] 
                  HAVING COUNT(*) = 1)

Incase that there are much less dpuls and tribles productids, you can also use

SELECT 
[ID], [PbMID], [Company]
FROM
Table1
WHERE [PbMID] NOT IN ( SELECT  [PbMID] 
                   FROM table1 
                  GROUP BY [PbMID] 
                  HAVING COUNT(*) > 1)

example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.