Select products in a range of discounted values?

0

I'm trying to make a filter for values for a virtual store, I have a product table, one for stock and one for promotions.

  

Products

CREATE TABLE 'produtos' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'codigo' VARCHAR(255) NULL DEFAULT NULL,
    'nome' VARCHAR(50) NULL DEFAULT NULL,
    'descricao' TEXT NULL,
    'info' TEXT NULL,
    'preco' DOUBLE(10,2) NULL DEFAULT NULL,
    'categorias' VARCHAR(255) NULL DEFAULT NULL,
    'subcategorias' VARCHAR(255) NULL DEFAULT '0',
    'marca' INT(11) NULL DEFAULT NULL,
    'img' VARCHAR(255) NULL DEFAULT NULL,
    'peso' INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY ('id'),
    UNIQUE INDEX 'codigo' ('codigo')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=14
;
  

stock

CREATE TABLE 'estoque' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'tamanho' VARCHAR(50) NOT NULL DEFAULT '0',
    'quantidade' INT(11) NOT NULL DEFAULT '0',
    'chave' INT(11) NOT NULL DEFAULT '0',
    'cor' VARCHAR(7) NULL DEFAULT NULL,
    PRIMARY KEY ('id')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=22
;
  

promotions

CREATE TABLE 'promocoes' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'descricao' TEXT NOT NULL,
    'desconto' VARCHAR(255) NULL DEFAULT NULL,
    'itens' VARCHAR(255) NULL DEFAULT NULL,
    'nome' VARCHAR(50) NULL DEFAULT NULL,
    'img' VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY ('id')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

What I try to do is to select the products first by looking for the value if it is in promotion, but if it is not in promotion it gets the price null in the calculation and is ignored.

In the example below I assumed the selection of a product from 15.00 to 30.00 reais.

SELECT 
    produtos.id,
    produtos.nome,
    produtos.preco,
    produtos.img 
FROM produtos 
LEFT JOIN estoque 
    ON produtos.id=estoque.chave 
LEFT JOIN promocoes 
    ON FIND_IN_SET(produtos.id,promocoes.itens) 
WHERE 
    FIND_IN_SET(1,subcategorias) && 
    (produtos.preco-((produtos.preco*promocoes.desconto)/100))>=15
    (produtos.preco-((produtos.preco*promocoes.desconto)/100))<=30

I tried to use the case in WHERE to make the selection, but there was no success.

I would like it in case the product is on sale that it did the filter as above and when it is not in promotion that filter based on the normal price.

    
asked by anonymous 17.11.2014 / 13:07

1 answer

3

I believe the COALESCE function resolves your issue.

COALESCE(promocoes.desconto,0)

returns 0 if promocoes.desconto is null .

Now, in fact, its structure seems a bit messy. Promotion and product indicate a relation M: N which would need another table that relates the two entities. Its structure also lacks foreign keys.

I mounted this SQLFiddle , which indicates this rewriting and modifying SELECT to use COALESCE .

    
17.11.2014 / 14:12