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.