Conditions in the SQL

Question

Hi, I have a question in SQL. In the company system there are several registered products, and these products have different sales unit, that is, some are sold by Package, Box, Unit and etc... Every product is sold per UNIT = 1, but there are products that when it is sold in another unit of sale, there is a reduction in its price to '0.98', example: Product Screw:

Sales Unit - price factor_price

UN(Unit) - 1

CX(Box) - 1

EN(package) - 0.98

I wanted to do a following, if the sales units had price factor all '1', then I would ignore this product... But if any sales unit is equal to '0.98' I take '0.98' + the other sales units with the values. In the screw example: As there is a sales unit that has the price '0.98'(PT), I would take the '1'(CX) and the '1'(UN) also... But if the price factor of all sales units of this product were '1' then I ignored the same...

I tried this Code:

SELECT 
produto.cd_prod,
produto.descricao,
unid_prod.qtde_unid,
unid_prod.fator_preco,
unid_vda
FROM 
produto,
unid_prod
WHERE
unid_prod.cd_prod = produto.cd_prod
AND unid_prod.fator_preco IN('0.9800', '1')

However in this query the database brings the data of the 2 without filtering, even if the product does not have 0.9800 it brings...

Answer 1

Taking the tables as an example:

produto                       unid_prod

cd_prod descricao             cd_prod fator_preco unid_vda
------- ------------          ------- ----------- --------
p1      Produto 1             p1      1           CX
p2      Produto 2             p1      1           UN
p3      Produto 3             p2      0.98        PT
                              p2      1           UN
                              p3      1           UN
                              p3      0.98        PT

You want to assemble a query that returns product information p2 and p3, because they have at least one fator_preco = 0.98, and does not return the product p1 because it does not contemplate this condition.

The desired result would be the following:

cd_prod descricao    fator_preco unid_vda
------- ------------ ----------- --------
p2      Produto 2    0.98        PT
p2      Produto 2    1           UN
p3      Produto 3    1           UN
p3      Produto 3    0.98        PT

One way to achieve this result would be to make a JOIN between the tables produto and unid_prod by placing a subquery in the clause WHERE to verify that the product has at least one record on unid_prod that has fator_preco = '0.9800'.

The query would look like this:

SELECT produto.cd_prod,
       produto.descricao,
       unid_prod.qtde_unid,
       unid_prod.fator_preco,
       unid_vda
  FROM produto
  JOIN unid_prod ON unid_prod.cd_prod = produto.cd_prod
 WHERE EXISTS(SELECT *
                FROM unid_prod as un
               WHERE un.cd_prod = produto.cd_prod
                 AND un.fator_preco = '0.9800')

Answer 2

To bring all products and unit case products exists some unit 0.98, just use the clause EXISTS by means of a sub-consultation:

SELECT produto.cd_prod, produto.descricao, unid_prod.qtde_unid, unid_prod.fator_preco, unid_prod.unid_vda 
FROM produto, unid_prod 
WHERE unid_prod.cd_prod = produto.cd_prod 
AND EXISTS 
    ( SELECT 1 FROM unid_prod subUnid 
    WHERE subUnid.cd_prod produto.cd_prod AND subUnid.fator_preco = '0.9800' )