CODEWITHSUNDEEP
sqlpostgresqlrelational-division
Yassin Hajaj
Yassin Hajaj

Reputation: 21975

SQL simplification possible?

I'm asked the following question concerning the table below

Display NPRO and LIBELLE from SAPIN products that are ordered only in Toulouse

Here is my answer (it is correct) but I feel like it could be simplified somehow.

Is there a way to make it easier?

select npro, libelle
from produit
where libelle like '%SAPIN%'
and npro in 
(select npro from detail where ncom in
(select ncom from commande where ncli in
(select ncli from client where localite='Toulouse')))
and npro not in
(select npro from detail where ncom in
(select ncom from commande where ncli in
(select ncli from client where localite<>'Toulouse')))

enter image description here

Upvotes: 0

Views: 94

Answers (2)

wildplasser
wildplasser

Reputation: 44240

EXISTS+ NOT EXISTS is the simplest. (IMHO)

SELECT npro, libelle
FROM produit p
WHERE p.libelle LIKE '%SAPIN%'
AND EXISTS (
        SELECT *  
        FROM detail d  
        JOIN commande co ON co.ncom = d.ncom
        JOIN client cl ON cl.ncli = co.ncli
        WHERE d.npro = p.npro
        AND cl.localite = 'Toulouse'
        )
AND NOT EXISTS (
        SELECT *
        FROM detail d
        JOIN commande co ON co.ncom = d.ncom
        JOIN client cl ON cl.ncli = co.ncli
        WHERE d.npro = p.npro
        AND cl.localite <> 'Toulouse'
        );

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

One way to make it easier to read is to use:

SELECT p.npro, p.libelle
FROM produit p
JOIN detail d
  ON p.npro = d.npro
JOIN commande c
  ON d.ncom = c.ncom
JOIN client cl
  ON cl.ncli = c.ncli
WHERE p.libelle LIKE '%SAPIN%'
GROUP BY p.npro, p.libelle
HAVING COUNT(CASE WHEN cl.localite = 'Toulouse' THEN 1 END) > 0
   AND COUNT(CASE WHEN cl.localite <> 'Toulouse' THEN 1 END) = 0

How it works:

  • join tables that you need
  • filter from products p.libelle that contains SAPIN
  • group by p.npro and p.libelle
  • filter result of aggregation: first count localite equal Touluse and with second count other localite.

Upvotes: 1

Related Questions