Geek Answers Handbook

How to find manufacturers that sell one type, but not the other?

I need:

Find manufacturers that sell PCs but not laptops.

This query does not display the correct result:

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

The way out of this and the correct conclusion:

output

Result given from this query:

 SELECT maker, type FROM product

ex2

Table layout:

table

Even this one does not work:

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

NOTE - type added for clarification only

+6
source share
12 answers

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
+7
source

Try it. Find out manufacturers who sell PCs but not laptops.

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

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

  • Trivial decision.

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

  • Using 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

  • Using EXCEPT .

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

  • Using 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 )

  • Using increment CASE 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

  • Using 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

  • Using HAVING and cheating ( HAVING cannot contain columns without an aggregate, so we use a meaningless (in this case) MIN aggregate (or MAX - it doesn't 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'
+3
source 
 select distinct maker from product where type = 'PC' and maker not in ( select maker from product where type = 'Laptop')

works

+1
source 
 SELECT DISTINCT maker FROM Product AS pc_product WHERE type = 'pc' AND NOT EXISTS (SELECT maker FROM Product WHERE type = 'laptop' AND maker = pc_product.maker );
+1
source

May I suggest that you trim the values ​​in the where clause ... there may be some errand spaces, and therefore an exact match does not return the desired result.

Same:

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

you can just try:

 SELECT maker , type FROM product WHERE TRIM(type) = 'PC'

If the laptop and PC are two separate types, just filter for the PC.

0
source

Try as below:

  Select maker from product where type in('PC','laptop') except select maker from product where type='laptop'
0
source

select an individual manufacturer from the product where type = 'PC' and the creator is not in (select the manufacturer of the product where type = 'laptop')

0
source

An easy way to solve a problem.

 Select maker from product where type = 'PC' Except Select maker from product where type = 'laptop'
0
source 
 select distinct Prd.maker as Maker from product as Prd INNER JOIN PC as P ON Prd.model = P.model WHERE NOT EXISTS ( SELECT 1 FROM Laptop as L INNER JOIN Product as P ON L.model = P.model and Prd.maker = P.maker )
0
source

Try this request

 SELECT DISTINCT MAKER FROM PRODUCT WHERE TYPE IN ('PC') AND MAKER NOT IN (SELECT MAKER FROM PRODUCT WHERE TYPE IN ('LAPTOP'))
0
source 
 select maker from Product where type in('PC') intersect select maker from Product where type in ('PC','Printer') except select maker from Product where type='Laptop'
-2
source

More articles:


All Articles