SELECT dep.titulo as departamento, dep.idDepartamento, cat.titulo as categoria, cat.idCategoria ,sub.titulo as subcategoria, sub.idSubCategoria FROM subCategorias sub
leftjoin categorias cat on cat.idCategoria = sub.idCategoria
leftjoin departamentos dep on dep.idDepartamento = cat.idDepartamento
where sub.idSubCategoria =11and cat.idCategoria=1
selectCOUNT(*) AS number_items,
group_concat( distinct( B.precio_final ) separator "," ) as precios,
group_concat( distinct( B.talla ) separator "," ) as tallas,
group_concat( distinct( B.marca ) orderby B.marca separator "," ) as marcas from (select A.*, ROUND((A.precio * (1-(A.porcent_descu*0.01))),2) as precio_final from (
select pr.sku, pr.nombre,pr.categoria, pr.subCategoria,pr.departamento, pr.imagen1, pr.precio, pr.nuevo, pr.created_at,coalesce(pr.marca, '') as marca, coalesce(pr.talla, '') as talla,
GREATEST( COALESCE(d_cat.Descuento,0), COALESCE(d_dpart.Descuento,0),COALESCE(d_sub.Descuento,0) ) as porcent_descu,
subc.idSubCategoria, cat.idCategoria
from productos pr
leftjoin descuentos d_cat
on
pr.departamento = d_cat.departamento_id AND pr.categoria = d_cat.categoria_id AND d_cat.subCategoria_id ISNULLAND now() BETWEEN d_cat.FechaInicio AND d_cat.FechaFin
leftjoin descuentos d_dpart
on
pr.departamento = d_dpart.departamento_id AND d_dpart.categoria_id ISNULLAND d_dpart.subCategoria_id ISNULLAND now() BETWEEN d_dpart.FechaInicio AND d_dpart.FechaFin
leftjoin descuentos d_sub
on
pr.departamento = d_sub.departamento_id AND pr.categoria = d_sub.categoria_id AND d_sub.subCategoria_id =pr.subCategoria
AND now() BETWEEN d_sub.FechaInicio AND d_sub.FechaFin
leftjoin departamentos dep on dep.idDepartamento = pr.departamento
leftjoin categorias cat on cat.idCategoria = pr.categoria
leftjoin subCategorias subc on subc.idSubCategoria = pr.subCategoria
where
pr.status=0AND pr.retorno=0AND vendido =0AND pr.categoria =1and pr.subCategoria =11GROUPby pr.sku ) as A) as B where `B`.`precio_final` between0.99and1000
select A.*, ROUND((A.precio * (1-(A.porcent_descu*0.01))),2) as precio_final from (
select pr.sku, pr.nombre,pr.categoria, pr.subCategoria,pr.departamento, pr.imagen1, pr.precio, pr.nuevo, pr.created_at,coalesce(pr.marca, '') as marca, coalesce(pr.talla, '') as talla,
GREATEST( COALESCE(d_cat.Descuento,0), COALESCE(d_dpart.Descuento,0),COALESCE(d_sub.Descuento,0) ) as porcent_descu,
subc.idSubCategoria, cat.idCategoria
from productos pr
leftjoin descuentos d_cat
on
pr.departamento = d_cat.departamento_id AND pr.categoria = d_cat.categoria_id AND d_cat.subCategoria_id ISNULLAND now() BETWEEN d_cat.FechaInicio AND d_cat.FechaFin
leftjoin descuentos d_dpart
on
pr.departamento = d_dpart.departamento_id AND d_dpart.categoria_id ISNULLAND d_dpart.subCategoria_id ISNULLAND now() BETWEEN d_dpart.FechaInicio AND d_dpart.FechaFin
leftjoin descuentos d_sub
on
pr.departamento = d_sub.departamento_id AND pr.categoria = d_sub.categoria_id AND d_sub.subCategoria_id =pr.subCategoria
AND now() BETWEEN d_sub.FechaInicio AND d_sub.FechaFin
leftjoin departamentos dep on dep.idDepartamento = pr.departamento
leftjoin categorias cat on cat.idCategoria = pr.categoria
leftjoin subCategorias subc on subc.idSubCategoria = pr.subCategoria
where
pr.status=0AND pr.retorno=0AND vendido =0AND pr.categoria =1and pr.subCategoria =11GROUPby pr.sku ) as A