0
I’m trying to make a value filter for a virtual store, I have a product table, a stock and another 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
;
stockpile
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 select the product first seeking the value if it is on sale, but if it is not on sale 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 makes the filter as above and when it is not on sale that filter based on normal price.
@Jorgeb. The problem is when the product is not discounted, then the calculation in the Where clause is null and the product is ignored, in which case I wanted it to take the value of the product instead of calculating the discount to use in Where
– RodrigoBorth
In that case you can use
UNIONto unite with promotions and those without promotions.– Jorge B.
Your SQL is really wrong. AND is not with && and fields brought by left John do not participate in Where, since they can return more than 1 record per selection... The promotions table with SET field for products is also not correct, and the categories should also be linked in a related table. If you’re at the beginning of the project, remodel your entire database. If it’s not at the beginning, you and your product will suffer a little from the poor structure.
– Marcelo Gomes
since when I can’t use && in place of AND?
– RodrigoBorth
@Marcelogomes mysql accepts yes
&&(even if it seems bizarre, it accepts) http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html– gmsantos
@gmsantos My fault. After I answered, I went to search. But not content with what I found, I searched more, and yes, mysql accepts, but it is not an SQL convention, mysql being one of the only ones to accept this. So, if you port to another comic, it will not work and rework the SQL commands.
– Marcelo Gomes