SQL: Need to select an answer which omits results which are not exclusive to categories

Question

Suppose my tables look something like this:

Manufacturer |Model         | Device type
----------------------------------------
Test          001             Desktop
Test          002             Laptop
Test          003             Tablet
Test2         004             Desktop
Test2         005             Laptop
Test2         006             Tablet
Test3         007             Desktop
Test3         008             Desktop
Test4         009             Tablet
Test4         010             Tablet

model         |          price
------------------------------
003                      100
006                      150
009                      175
010                      190

How do I display:

Manufacturer
----------
Test4

I'm only looking to display the names of manufacturers that produce Tablets exclusively. If a manufacturer produces any other type of device including tablets, I want to omit them from displaying. Test4 is the only one to produce tablets only, so that is the result I want.

Thanks again for the help, I'm still pretty new to SQL and this would be a good help.

I've tried: SELECT manufacturer FROM tablename WHERE devicetype NOT IN ('laptop', 'desktop') AND type='tablet'; I've also tried: SELECT manufacturer FROM tablename Type NOT IN (SELECT type FROM Product WHERE Type = 'Laptop' AND Type = 'Desktop') AND type='Tablet' . I still Test,Test2 & Test4 as my answers — t0tallyn0tab0t, Nov 05 '18 at 06:55

Lee Mac · Answer 1 · 2018-11-05T11:26:51.910

You could also achieve this using aggregation, e.g.:

SELECT a.Manufacturer
FROM Table1 a
GROUP BY a.Manufacturer
HAVING 
    MIN(a.[Device type]) = MAX(a.[Device type]) AND
    MIN(a.[Device type]) = 'Tablet'

The above verifies that a Manufacturer only produces one Device Type by checking whether the minimum (in a sense of sorting order - see here) of the Device Type is equal to the maximum Device Type, and then selecting those with a minimum of Tablet.

Or with nested aggregation, e.g.:

SELECT DISTINCT c.Manufacturer
FROM
    Table1 c INNER JOIN
    (
        SELECT b.Manufacturer
        FROM
        (
            SELECT a.Manufacturer, a.[Device type]
            FROM Table1 a 
            GROUP BY a.Manufacturer, a.[Device type]
        ) b
        GROUP BY b.Manufacturer
        HAVING COUNT(*) = 1
    ) d
    ON c.Manufacturer = d.Manufacturer
WHERE 
    c.[Device type] = 'Tablet'

Here, the innermost nested query selects Manufacturers and the Device Types they produce, from which Manufacturers which only produce one Device Type are selected. The result of this is then INNER JOINed to the original table so that the Device Type may be selected.

Replace Table1 with your table name.

Fahmi · Accepted Answer · 2018-11-05T11:37:51.253

0

You can try below using correlated subquery -

SELECT DISTINCT a.Manufacturer
FROM tablename a
WHERE NOT EXISTS(
    SELECT 1 FROM tablename b 
    WHERE a.Manufacturer = b.Manufacturer AND b.deviceType not in ('Tablet'))

edited Nov 05 '18 at 11:37

answered Nov 05 '18 at 07:03

Fahmi

37,315
5
22
31

I've tried this answer, unfortunately, the only thing that could relate my first table with my second is the model, rather than the manufacturer. Using the model for "a.Manufacturer = b.Manufacturer" , so it would be "a.model = b.model". Using this current answer shows me all manufacturers. – t0tallyn0tab0t Nov 05 '18 at 07:30
@t0tallyn0tab0t, the above query is only based on your first table - not tow table, so you execute this using first table then you can join with your 2nd table – Fahmi Nov 05 '18 at 07:32
1

I think there is a misunderstanding: `b` is not your second table, but an alias for a second usage of your first table. Maybe this makes it more clear: `SELECT DISTINCT a.Manufacturer FROM YourFirstTable a WHERE NOT EXISTS(SELECT * FROM YourFirstTable b WHERE a.Manufacturer = b.Manufacturer AND b.deviceType not in ('Tablet'))`. So there are two aliases (`a` and `b`) for your table `YourFirstTable` in the query. – AHeyne Nov 05 '18 at 07:46
@fa06 you could optionally change `SELECT *` to `SELECT 1` for performance, since only one record is required to validate (or invalidate) the `WHERE` clause. – Lee Mac Nov 05 '18 at 11:28

SQL: Need to select an answer which omits results which are not exclusive to categories

2 Answers2