How do I find the makers that sell one type but not another?

Question

I have to:

Find the makers that sell PCs but not laptops.

This query is not outputting correct result:

SELECT maker, type FROM product
WHERE type = 'PC' AND type <> 'Laptop'

Output from this and correct output:

Result set from this query:

SELECT maker, type FROM product

Table schema:

Even this one is not working:

SELECT maker, type FROM product
WHERE type IN('PC') AND type NOT IN('Laptop')

NOTE - type is added just for clarification

Answer 1

Try:

SELECT maker
FROM product
GROUP BY maker
HAVING SUM(CASE WHEN type = 'PC' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN type = 'Laptop' THEN 1 ELSE 0 END) = 0

Answer 2

There are many exotic ways to solve this problem. Here, the first coming to mind :)

1. The trivial solution.

   SELECT DISTINCT maker
      FROM Product
        WHERE type = 'PC' AND maker NOT IN (
            SELECT maker
            FROM Product
            WHERE type = 'Laptop')
   

2. Using the JOIN

   SELECT DISTINCT Pr.maker
   FROM Product AS Pr LEFT JOIN Product AS Pr2
       ON Pr.maker = Pr2.maker AND Pr2.type = 'Laptop'
   WHERE Pr.type = 'PC' AND Pr2.maker IS NULL
   

3. Using the EXCEPT.

   SELECT DISTINCT maker
       FROM product AS Pr1
       WHERE type = 'PC'
   EXCEPT
   SELECT DISTINCT Pr2.maker
       FROM product AS Pr2
       WHERE type = 'laptop'
   

4. Using the DISTINCT and NOT EXISTS.

   SELECT DISTINCT maker
   FROM Product AS PcP
   WHERE type = 'PC' AND
         NOT EXISTS (SELECT maker
                     FROM Product
                     WHERE type = 'laptop' AND
                     maker = PcP.maker
                    )
   

5. Using the CASE increment and HAVING.

   SELECT maker
       FROM product
   GROUP BY maker
   HAVING SUM(CASE WHEN type = 'PC' THEN 1 ELSE 0 END) > 0
      AND SUM(CASE WHEN type = 'Laptop' THEN 1 ELSE 0 END) = 0
   

6. Using the DISTINCT and comparison subqueries

   SELECT DISTINCT maker
   FROM Product AS Pr
   WHERE (SELECT COUNT(1)
          FROM Product AS Pt
          WHERE Pt.type = 'PC' AND Pt.maker = Pr.maker
         ) > 0 
       AND
         (SELECT COUNT(1)
          FROM Product AS Pt2
          WHERE Pt2.type = 'Laptop' AND
          Pt2.maker = Pr.maker
         ) = 0
   

7. Using HAVING and cheating (The HAVING can't contain columns without aggregate, so we use the senseless(in this case) aggregate MIN (or MAX - it does not matter)

   SELECT maker
       FROM (SELECT DISTINCT maker, type
             FROM Product
             WHERE type IN ('PC', 'Laptop')
            ) AS T
   GROUP BY maker
   HAVING COUNT(*) = 1 AND MIN(type) = 'PC'
   

Answer 3

Try this Find out the makers that sale PCs but not laptops.

SELECT maker , type FROM product
WHERE type = 'PC' AND maker NOT IN 
( SELECT maker FROM product WHERE type = 'laptop' )

Answer 4

select distinct maker 
from product 
where type = 'PC'  
  and maker not in ( select maker from product where type = 'Laptop')

it works