How to exclude non-distinct rows in a query

Question

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.

Answer 1

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;

Answer 2

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

Answer 3

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