2

I have 2 queries:

Query1:

SELECT DISTINCT maker FROM Product WHERE type='laptop'

Gives result {A, B, E, F, G}

and

Query2:

SELECT DISTINCT maker FROM Product WHERE type='pc'

Gives result {A, B, C, D, E}

The question is: How can I get query1 - query2 = {A, B, E, F, G} - {A, B, C, D, E} = {F, G}

I tried:

SELECT DISTINCT maker FROM Product WHERE type='pc' AND NOT EXISTS (
    SELECT DISTINCT maker FROM Product WHERE type='b'
);

But it gave me empty record. Sorry I'm new with MySQL

  • The inner query always returns something, so the NOT EXISTS is always false - you need to limit it to the maker you are just checking. Or you can use maker NOT IN(..) instead of NOT EXISTS to do the same. – jkavalik Sep 25 '15 at 5:51
5

You might use NOT IN instead:

SELECT DISTINCT maker 
FROM Product 
WHERE type='pc' 
  AND maker  NOT IN
 (
    SELECT maker 
    FROM Product
    WHERE type='b'
      AND maker IS NOT NULL -- to prevent empty results
 );

Caution: This will not work correctly with a NULL maker.

The recommended solution is NOT EXISTS, but you need a Correlated Subquery:

SELECT DISTINCT maker 
FROM Product AS p1
WHERE type='pc'
  AND NOT EXISTS
 (
    SELECT maker
    FROM Product AS p2
    WHERE type='b'
      AND p1.maker = p2.maker -- correlation to the outer SELECT
 );

If MySQL supported EXCEPT it would be a simple

SELECT maker FROM Product WHERE type='pc'
EXCEPT
SELECT maker FROM Product WHERE type='b';
2

Another option is to use grouping and check the results of certain aggregated values:

SELECT
  maker
FROM
  Product
GROUP BY
  maker
HAVING
  COUNT(type = 'laptop' OR NULL) > 0
  AND COUNT(type = 'pc' OR NULL) = 0
;

The HAVING conditions specify that each maker group must have at least one type = 'laptop' row and no type = 'pc' rows.

Basically, the COUNT expression return the number of rows matching the specified type, for each group. (A detailed explanation of the purpose of OR NULL in these COUNTs can be found in this Stack Overflow answer.) You could replace the COUNTs with equivalent SUMs:

HAVING
  SUM(type = 'laptop') > 0
  AND SUM(type = 'pc') = 0

and thus make the expressions much shorter without changing the results. It is just a matter of preference for me to use COUNT when it is about counting.

Note: The query uses the type values from your description rather than from your attempted query.

1

I have two suggestions

SUGGESTION #1

You should put each query in a subquery then do a LEFT JOIN

SELECT A.maker
FROM      (SELECT DISTINCT maker FROM Product WHERE type='laptop') A
LEFT JOIN (SELECT DISTINCT maker FROM Product WHERE type='pc')     B
USING (maker) WHERE B.maker IS NULL;

SUGGESTION #2

Add this compound index to speed up the subqueries

ALTER TABLE Product ADD INDEX type_maker_ndx (type,maker);

GIVE IT A TRY !!!

CAVEAT

Please note you said you tried this

SELECT DISTINCT maker FROM Product WHERE type='pc' AND NOT EXISTS (
    SELECT DISTINCT maker FROM Product WHERE type='b'
);

You probably meant

SELECT DISTINCT maker FROM Product WHERE type='pc' AND NOT EXISTS (
    SELECT DISTINCT maker FROM Product WHERE type='laptop'
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.