CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 13

Returning rows where 1 column is the same and other column is different

I'm sure this is very simple, but i'm struggling!

What i want to do is to return the ProductID from the table below where Mailed = True but only where ProductID has a single supplier.

In the example below, i need to return PRoductIDs 2 and 5 but not 1

+-----------+----------+-------------+
| ProductID | Supplier | CanBeMailed |
+-----------+----------+-------------+
|         1 | A        |      False  |
|         1 | B        |      True   |
|         2 | C        |      True   |
|         3 | C        |      False  |
|         4 | D        |      False  |
|         4 | E        |      False  |
|         5 | E        |      True   |
|         6 | F        |      False  |
+-----------+----------+-------------+

Any thoughts?

Thanks

Upvotes: 1

Views: 54

Answers (1)

Thom A
Thom A

Reputation: 95554

This should work for what you're after:

WITH VTE AS (
    SELECT *
    FROM (VALUES (1,'A',0),
                 (1,'B',1),
                 (2,'C',1),
                 (3,'C',0),
                 (4,'D',0),
                 (4,'E',0),
                 (5,'E',1),
                 (6,'F',0)) V(ProductID, Supplier, Mailed))
SELECT ProductID
FROM VTE
GROUP BY ProductID
HAVING SUM(CASE Mailed WHEN 'True' THEN 1 ELSE 0 END) > 0
   AND COUNT(DISTINCT Supplier) = 1;

Upvotes: 4

Related Questions