Query which excludes elements meeting a specific condition

Question

I'm facing a problem trying to develop a query for this simple database I've created. It gathers 3 suppliers identified by a code (CODICEFORNITORE) which sells products such as phone or cereals, each one of them identified by another code (CODICEPRODOTTO). What I'm trying to accomplish is to get back the number of suppliers who do not sell any Apple product. The association between the products and its supplier is tracked thanks to a third table called Catalogue (CATALOGO).

I've thought that the best way to do that is by a EQUI JOIN between PRODUCT TABLE and CATALOGUE TABLE, then trying to count the number of Apple products sell by suppliers and then exclude those whose count is less or equal to 0. But I can't succed in creating such a query. I have no problem in counting how many suppliers sell Apple products instead.

Achieving the opposite is quite simple (and I'm sure I could do it even in a easier way):

SELECT COUNT(CODICEFORNITORE) AS NUMERO_FORNITORI_APPLE --- This the number of apple supplier
FROM CATALOGO C JOIN PRODOTTI P ON C.CODICEPRODOTTO = P.CODICEPRODOTTO
GROUP BY CODICEFORNITORE, MARCA
HAVING Marca = 'Apple';
--- this returns 1 as expected ---

What I'm trying to achieve should return '2' based on the following table

table Thanks in advance and sorry for poor english

(Obviously) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Just googling your title with 'sql ite:stackoverflow.com' returns answers. — philipxy, May 12 '19 at 23:03
Possible duplicate of [How to exclude records with certain values in sql select](https://stackoverflow.com/questions/15389091/how-to-exclude-records-with-certain-values-in-sql-select) — philipxy, May 12 '19 at 23:04
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, May 12 '19 at 23:05

score 1 · Accepted Answer · answered May 12 '19 at 13:46

You can get the list of suppliers who supply at least one product and have no Apple products using:

SELECT CODICEFORNITORE
FROM CATALOGO C JOIN
     PRODOTTI P
     ON C.CODICEPRODOTTO = P.CODICEPRODOTTO
GROUP BY CODICEFORNITORE
HAVING SUM(CASE WHEN Marca = 'Apple' THEN 1 ELSE 0 END) = 0;

And you can get the count using a subquery:

SELECT COUNT(*)
FROM (SELECT CODICEFORNITORE
      FROM CATALOGO C JOIN
           PRODOTTI P
           ON C.CODICEPRODOTTO = P.CODICEPRODOTTO
      GROUP BY CODICEFORNITORE
      HAVING SUM(CASE WHEN Marca = 'Apple' THEN 1 ELSE 0 END) = 0
     ) c;

Alternatively, if you have a table of suppliers, you can use NOT EXISTS:

SELECT COUNT(*)
FROM FORNITORE F
WHERE NOT EXISTS (SELECT 1
                  FROM CATALOGO C JOIN
                       PRODOTTI P
                       ON C.CODICEPRODOTTO = P.CODICEPRODOTTO
                  WHERE F.CODICEFORNITORE = C.FORNITORE AND
                        P.Marca = 'Apple'
                 );

Oh I didn't know about the possibility to use a "CASE" clause. I will gather more infos about that. What does it mean the c at the end of the subquery? — King Powa, May 12 '19 at 13:49
The solution with NOT EXISTS works fine. I will try the first one too. Sorry for not providing additional informations about attribute names. QUICK UPDATE: also the first one is working (i deleted the c at the end.) — King Powa, May 12 '19 at 13:50
@KingPowa . . . The `NOT EXISTS` may have better performance. Note that it will also return suppliers with no products, if that is desirable. — Gordon Linoff, May 12 '19 at 13:52

Query which excludes elements meeting a specific condition

1 Answers1