Conditions in SQL

Navigation
1

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

Sales unit - price_factor

UN (Unit) - 1

CX (Cash) - 1

PT (Package) - 0.98

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

I've 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')

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

    
asked by anonymous 13.12.2017 / 00:52

2 answers

0

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 mount a query that returns the information for the p2 and p3 products, because they have at least fator_preco = 0.98 , and do not return the p1 product because it does not.

The desired result would be as follows: 

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 JOIN between the produto and unid_prod tables by placing a subquery in the WHERE clause to verify that the product has at least one unid_prod 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')
    
13.12.2017 / 01:12
0

In order to bring all the products and unit products if there is a 0.98 unit, just use the EXISTS clause through a sub query: 

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' )
    
13.12.2017 / 01:19
redirect () - back () return with variable Ionic ng-if comparing values