consulta en mysql sobre 3 tablas

[PabloManuel]

estoy parado y no puedo continuar, es urgente gracias.

estoy terminando un módulo de ecommerce y estoy con la presentación de los pedidos, esta es la estructura de tablas:

Código MySQL:

CREATE TABLE `usuarios` (
  `Id` int(11) NOT NULL auto_increment,
  `Nombre` varchar(20) default NULL,
  `Csena` varchar(20) default NULL,
  `Id_grupo` int(11) NOT NULL,
  PRIMARY KEY  (`Id`),
  KEY `Id_grupo` (`Id_grupo`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=151 ;
 
CREATE TABLE `ec_pedidos` (
  `Id` int(9) NOT NULL auto_increment,
  `Id_User` int(11) NOT NULL,
  `Fecha_Ped` date NOT NULL,
  `IVA_aplicado` int(3) NOT NULL,
  `Bimponible` decimal(9,2) NOT NULL,
  PRIMARY KEY  (`Id`),
  KEY `Id_User` (`Id_User`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;
 
CREATE TABLE `ec_pedidos_linest` (
  `Id` int(9) NOT NULL auto_increment,
  `Id_Pedido` int(9) NOT NULL,
  `Id_Estado` int(9) NOT NULL,
  `FechaHora` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Comentario` varchar(150) NOT NULL,
  PRIMARY KEY  (`Id`),
  KEY `Id_Estado` (`Id_Estado`),
  KEY `Id_Pedido` (`Id_Pedido`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=64 ;
 
CREATE TABLE `ec_pedidos_est` (
  `Id` int(9) NOT NULL auto_increment,
  `Nombre` varchar(50) NOT NULL,
  `Color` varchar(10) NOT NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
 

los pedidos los presento en una tabla de la siguiente manera:


Mi problema viene a la hora de usar el buscador de arriba, sobre todo en el estado.

el pedido guarda todos los estados que ha tenido con su fecha, siendo el estado actual el que tenga la fecha más reciente según el número de pedido.

cuando uso el buscador, tengo que generar una cláusula Where para anidarla al select y así mostrar los resultados de la búsqueda paginados, pero no hay manera.

Esto es a lo máximo que llego:

Código MySQL:

SELECT DISTINCT Pe.id, pe.Su_Referencia, pe.Id_User, pe.Fecha_ped, pe.bimponible, es.id_estado AS idest, MAX( es.fechahora ) AS ulti
FROM Usuarios AS Us
INNER JOIN (
EC_Pedidos_LinEst AS Es
INNER JOIN EC_Pedidos AS Pe ON Es.Id_Pedido = Pe.Id
) ON Us.Id = Pe.Id_User
GROUP BY Pe.id
LIMIT 0 , 30
 

Me devuelve todos los pedidos y la última fecha de las líneas de estado, pero no corresponde con el último id de estado, me devuelve el primero.

he intentado anidar select, pero tampoco lo he conseguido.

[PabloManuel]

Esto es lo que devuelve:


Pero si os fijáis en el último registro, esa fecha corresponde a un registro con estado 6 y no 1

¿por qué?

[PabloManuel]

¿Nadie sabe orientarme? Por lo menos saber que tipo de consulta usar, si subconsultas o bien usar el Limit.

[PabloManuel]

Código MySQL:

SELECT  Pe.id, 
        pe.Su_Referencia, 
        pe.Fecha_ped, 
        pe.bimponible, 
        pe.id_user, 
        (
            SELECT le.id_estado
            FROM Ec_Pedidos_LinEst AS Le
            WHERE 
                le.id_pedido = pe.id
            AND le.fechahora = ( 
                                SELECT MAX( fechahora ) 
                                FROM ec_pedidos_linest
                                WHERE id_pedido = pe.id 
                                )
        ) AS Estado
        FROM EC_Pedidos AS Pe
 

Con esta instrucción consigo mostrar todos los pedidos, ahora a ver el filtrado.

Si el usuario busca un estado del pedido, el sistema sólo debe de tener en cuenta el último, es decir, el que tenga la fecha más alta para ese pedido.

[Vallu]

Mirate este link. Aqui hay un ejemplo de como obtener el resultado mas alto dentro de un grupo.
Creo que te servira para distinguir que data es la mas grande para un pedido en concreto.
http://dev.mysql.com/doc/refman/5.0/...group-row.html

[PabloManuel]

Vaya, pues justamente saqué parte del código de ahí, creo que ya lo tengo, porque además necesito buscar por nombre de cliente, no puedo usar el ID_user porque quiero buscar por cadena de texto y un like

Me está quedando una consulta enorme, ya que en el where final tengo que repetir las subconsultas sino me da error

La siguiente consulta busca los pedidos con último estado =1 y que el nombre de usuario contenga loma:

Código MySQL:

SELECT Pe.id, pe.Su_Referencia, pe.Fecha_ped, pe.bimponible, pe.id_user, (
 
SELECT nombre
FROM usuarios
WHERE id = pe.id_user
) AS nombre, (
 
SELECT le.id_estado
FROM Ec_Pedidos_LinEst AS Le
WHERE le.id_pedido = pe.id
AND le.fechahora = ( 
SELECT MAX( fechahora ) 
FROM ec_pedidos_linest
WHERE id_pedido = pe.id )
) AS Estado
FROM EC_Pedidos AS Pe
WHERE (
 
SELECT le.id_estado
FROM Ec_Pedidos_LinEst AS Le
WHERE le.id_pedido = pe.id
AND le.fechahora = ( 
SELECT MAX( fechahora ) 
FROM ec_pedidos_linest
WHERE id_pedido = pe.id )
) =1
AND (
 
SELECT nombre
FROM usuarios
WHERE id = pe.id_user
) LIKE  '%loma%'
 

[PabloManuel]

¿La estructura es correcta?
¿Hay manera de no repetir las subconsultas en el Where final?

[gnzsoloyo]

Yo la probaría por acá:

Código MySQL:

SELECT 
    Pe.id, 
    pe.Su_Referencia, 
    pe.Fecha_ped, 
    pe.bimponible, 
    pe.id_user, 
    U.nombre, 
    epl.id_estado Estado
FROM EC_Pedidos Pe 
    INNER JOIN ec_pedidos_linest epl ON epl.id_pedido = pe.id
    INNER JOIN usuarios U ON U.id = pe.id_user
WHERE
    epl.fechahora = (SELECT MAX(fechahora)  FROM ec_pedidos_linest WHERE id_pedido = pe.id )
    AND U.nombre LIKE  '%loma%';
 

[PabloManuel]

funciona perfectamente, me muestra todos los que contenga loma en el nombre, pero...

¿y si además quiero mostrar sólo los pedidos con estado = 6?

por ejemplo el cliente querrá ver sólo los pedidos con estado "con artículos sin stock"

Después tengo es resto de campos que tengo que ir adinando al Where en caso de que haya datos.

Es una chorrada el resultado, un buscador de pedidos, nunca pensé que esta consulta me daría tantos problemas.

PD: Vallu, te he mandado el código, lo has recibido?

[gnzsoloyo]

¿y si además quiero mostrar sólo los pedidos con estado = 6?

por ejemplo el cliente querrá ver sólo los pedidos con estado "con artículos sin stock"

Después tengo es resto de campos que tengo que ir adinando al Where en caso de que haya datos.

No. Lo que haces es crear las consultas dinámicamente por medio de programación, agregando o sacando condiciones, según se necesite.
De todos modos lo mejor es definir previamente cuáles son las consultas que se pueden llegar a realizar, antes de diseñar las clases DAC que se comunicarán con la base para obtener los datos. Hacerlo sobre la marcha sólo te traerá dolores de cabeza.
Crear consultas a medida que el sistema está siendo programado es la mejor manera de terminar haciendo métodos redundantes o solapados.
Cuando puedas escribir en una sola hora todas las consultas básicas posibles que necesites, es muy, muy probable, que veas que muchas de esas se superponen en condiciones, por lo que bien podrías reducir la lógica de construcción a unos pocos SP o bien métodos de clase DAC.
Por supuesto que eso implica algo que los programadores no quieren hacer habitualmente: Planificar requerimientos.
Pero, bueno, es un consejo.

[PabloManuel]

pufff... se complica demasiado y todo por guardar todos los estados que ha tenido.

para mostrar los resultados uso una función que hace la paginación que es la siguiente, es decir, de la consulta principal sólo mostraré los resultados de la página solicitada.

en definitiva, tengo que crear la consulta, es dependerá si el cliente ha buscado o no, si no ha buscado muestro todos los registros paginados, si ha buscado, muestro la búsqueda paginada.

la función es esta:

Editado: Código de programación no permitido en foros de BBDD

creo que el sistema que tengo para mostrar resultados paginados de un select no es válido para esta consulta.

tube que crear esta función de esta manera porque con mysql no puedo usar el Recordcount ni absolutepage

por lo tanto antes de ejecutar la consulta ejecuto la consulta sólo con select count(*) para saber cuantos registros tengo que paginar

[PabloManuel]

he intentado más cosas:

dejar que la consulta me muestre todos los estados con lo cual se repite el id del pedido y solucionarlo con un distinct, pero de la tabla resultante no se queda con el último estado

quitar el distinct y al pintar la tabla sólo motrar un id de pedido con una cláusula IF

Pero ambas soluciones me parecen una chapuza. tiene que haber una sentencia select usando inner join que me permitar variar a antojo el where final, teniendo en cuenta que si el usuario no ha hecho búsqueda, este no existirá.

[gnzsoloyo]

No postees código de programación en estos foros.
Leer las normas de los foros de BBDD: http://www.forosdelweb.com/f86/norma...datos-1040766/

Aquí los temas son sólo BBDD y SQL. Para resolver el problema desde programación deberás consultar en el foro del lenguaje que usas.

[PabloManuel]

Código SQL:

SELECT CAMPOS
FROM PEDIDOS
WHERE
    REF_CLIENTE LIKE '%x%'
    AND
    NOMBRE_USER LIKE '%x%'
    AND
    ultimo_estado = x
    AND
    [CADENA PARA LA FECHA, BETWEEN; >=; <=]
    AND
        (
        RAZÓN_SOCIAL LIKE '%x%'
        OR
        DIRECCIÓN1 LIKE '%x%'
        OR
        LOCALIDAD LIKE '%x%'
        )
 

Esta sería a groso modo la consulta, dependerá de los campos que cumplimente en el buscador, ya que el campo "cualquier texto" buscará dicha cadena en 15 campos de la cabecera de pedidos.

Con respecto a las líneas del pedido (si un usuario quiere buscar un artículos vendido) no voy a dar esta opción aquí, si no en otro sítio.

el gran problema está en el último estado, que es el estado más reciente para ese pedido y se guardan en una tabla a parte, en la cabecera del pedido no aparece ningún estado.

he baradado dos opciones

incluir en pedido el último estado, lo cual me obliga a modificar la cabecera cada vez que se agregue un estado y duplicar información
y en la tabla de líneas incluir un campo como "activo" lo cual me obliga a desmarcar el anterior cada vez que meta uno nuevo o cuando el user modifique/borre alguno de estos, sería un descontrol.

[PabloManuel]

sigo mirando cosas.

Os pregunto ¿podría usar de alguna manera la cláusula SELECT GREATEST en vez de MAX() para evitar usar el GROUP BY y así quedarme con el mayor valor del campo fecha de las líneas de estado?

[gnzsoloyo]

Para eso existe el MAX().
GREATEST() tiene otro uso, no es una función de tabla, sino de registro, por lo que no aplica para el caso:

GREATEST(value1,value2,...)

Con dos o más argumentos, retorna el argumento mayor (con valor mayor). Los argumentos se comparan usando las mismas reglas que para LEAST().

Código MySQL:

mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST('B','A','C');
        -> 'C'
 

Vale decir, aplica a valores o campos diferentes que se encuentren en el mismo registro. No entre registros diferentes.

[PabloManuel]

Bueno, no tengo más remedio que seguir, de momento va a funcionar así y si me ofrecéis otra solución lo cambio, porque es engorroso a más no poder.

En caso de que el usuario no haga filtrado, saco todos los pedidos y los pagino con esta instrucción:

Código MySQL:

SELECT 
        Pe.id, 
        pe.Su_Referencia, 
        pe.Fecha_ped, 
        pe.bimponible, 
        pe.id_user,
        (select nombre from usuarios where id=pe.id_user) as nombre,
        (Select le.id_estado From Ec_Pedidos_LinEst As Le Where le.id_pedido=pe.id and le.fechahora=(select max(fechahora) from ec_pedidos_linest where id_pedido=pe.id)) As Estado
FROM 
        EC_Pedidos as Pe
 

Si el cliente realiza una búsqueda con uno o más campos le anidaré un Where quedando la siguiente consulta en caso de que cumplimente todos los campos del buscador:

Código MySQL:

SELECT 
        Pe.id, 
        pe.Su_Referencia, 
        pe.Fecha_ped, 
        pe.bimponible, 
        pe.id_user,
        (select nombre from usuarios where id=pe.id_user) as nombre,
        (Select le.id_estado From Ec_Pedidos_LinEst As Le Where le.id_pedido=pe.id and le.fechahora=(select max(fechahora) from ec_pedidos_linest where id_pedido=pe.id)) As Estado
FROM 
        EC_Pedidos as Pe
where 
    (Select le.id_estado From Ec_Pedidos_LinEst As Le Where le.id_pedido=pe.id and le.fechahora=(select max(fechahora) from ec_pedidos_linest where id_pedido=pe.id)) = 1
    AND
    (select nombre from usuarios where id=pe.id_user) Like '%loma%'
    And
    pe.su_referencia like '%89%'
    And
    pe.fecha_Ped >= '2003-05-05' | pe.fecha_Ped <= '2003-05-05' | pe.fecha_ped Between '2003-01-05' and '2003-12-05'
    And
        (
        pe.DF_RazonSocial like '%xxxxxx%'
        or
        pe.DF_CIF like '%xxxxxx%'
        or
        pe.DF_Direc1 like '%xxxxxx%'
        or
        pe.DF_Direc2 like '%xxxxxx%'
        or
        pe.DF_CP like '%xxxxxx%'
        or
        pe.DF_Localidad like '%xxxxxx%'
        or
        pe.DF_Provincia like '%xxxxxx%'
        or
        pe.DF_Pais like '%xxxxxx%'
        or
        pe.DE_Nombre like '%xxxxxx%'
        or
        pe.DE_Direc1 like '%xxxxxx%'
        or
        pe.DE_Direc2 like '%xxxxxx%'
        or
        pe.DE_CP like '%xxxxxx%'
        or
        pe.DE_Localidad like '%xxxxxx%'
        or
        pe.DE_Provincia like '%xxxxxx%'
        or
        pe.DE_Pais like '%xxxxxx%'
        or
        )
 

la línea
pe.fecha_Ped >= '2003-05-05' | pe.fecha_Ped <= '2003-05-05' | pe.fecha_ped Between '2003-01-05' and '2003-12-05'
afecta a la fecha del pedido de tres maneras, desde, hasta o entre, por lo tanto según si cumplimenta uno o dos campos de filtrado para fecha tendrá uno de los tres formatos.

[PabloManuel]

ya no me salen más ideas, mis conocimientos de mysql se quedan ahí y hasta que no haga el curso... más no sé dar.

yo creo que es una chapuza porque lo que quiero sacar de la base de datos no es nada del otro mundo, el problema sigue estando en el estado.

debe buscar el estado que quiera el cliente para ese pedido, pero no en todos, si no en el estado más reciente, que es el estado activo por llamarlo de alguna manera.

[gnzsoloyo]

Es que a mi entender te estás ahorgando en un dedal de agua. No es tan dificil ni engorroso crear consultas con condiciones que se incluyen dinámicamente. Quienes nos dedicamos al desarrollo lo hacemos constantemente y no es muy pesado.
Simplemente a nivel programático evaluamos lo que el usuario incluyó de lo que la aplicación le permite, y en base a ello se van agregando al WHERE las condiciones necesarias.
¿Es largo de programar?
Si.
¿Es difícil?
No.
¿Es eficiente?
Si.
¿Hay otras alternativas?
Depende. Si quieres crear consultas eficientes o mínimamente optimizadas, en realidad no. Porque te permite acotar las búsquedas y evitar traer resultados falsos o innecesario, lo que termina redundando en beneficio de la aplicación.
Ten en cuenta un principio básico: Si una consulta aplica índices, pero las condiciones del WHERE son tales que puede leer la mitad de la tabla, entonces leerá toda la tabla (full scan), porque descartará el índice... sin que tu te des cuenta.

Moraleja: Tomate el tiempo necesario, crea las consultas con criterios más específicos, y más eficientes. Y si eso implica crear 345 consultas y 300 funciones en la DAC para lograrlo, pues hazlas.

La aplicación que nosotros tenemos tiene más de 200 consultas sólo en un paquete de procedimientos, y tiene más de 500 de estos.
¿Ha hecho pesada a la aplicación?
No. Es muy eficiente. Porque cada consulta es CONCRETA, y por ende, no desperdicia recursos. La resolución de cuál llama está en el codebehind de la aplicación, no en la base.

[PabloManuel]

Bueno, gracias a ti y a vallu por vuestros consejos, ya está operativo y sí genera una sentencia enorme pero totalmente eficiente.

He optado por subconsultas en vez de cláusulas join para poder tener un Select y un from que mostrase todos los pedidos y necesidad de usar el Where, de esta forma el Where quedaría en blanco si el usuario no busca y mostraría todos los pedidos, si usa algún campo de búsqueda, genero el Where en función de los campos usados.

1º se mostrarán siempre los registros con esta sentencia a la cual se le anidará clausua Where o no, en función de si se busca o no:

Código MySQL:

SELECT 
        Pe.id, 
        pe.Su_Referencia, 
        pe.Fecha_ped, 
        pe.bimponible, 
        pe.id_user,
        (SELECT nombre FROM usuarios WHERE id=pe.id_user) as nombre,
        (SELECT le.id_estado FROM Ec_Pedidos_LinEst As Le WHERE le.id_pedido=pe.id AND le.fechahora=(SELECT max(fechahora) FROM ec_pedidos_linest WHERE id_pedido=pe.id)) As Estado
FROM 
        EC_Pedidos as Pe
 

2º Será siempre la misma y generaré y concatenaré la sentencia Where, este sería el formato completo si usa todos los campos de búsqueda:

Código MySQL:

WHERE 
    (SELECT le.id_estado FROM Ec_Pedidos_LinEst As Le WHERE le.id_pedido=pe.id AND le.fechahora=(SELECT max(fechahora) FROM ec_pedidos_linest WHERE id_pedido=pe.id)) = 1
    AND
    (SELECT nombre FROM usuarios WHERE id=pe.id_user) LIKE '%loma%'
    AND
    pe.su_referencia LIKE '%89%'
    AND
    pe.fecha_Ped >= '2003-05-05' | pe.fecha_Ped <= '2003-05-05' | pe.fecha_ped BETWEEN '2003-01-05' AND '2003-12-05'
    AND
        (pe.DF_RazonSocial LIKE '%xxxxxx%'  OR
        pe.DF_CIF LIKE '%xxxxxx%'  OR
        pe.DF_Direc1 LIKE '%xxxxxx%' OR
        pe.DF_Direc2 LIKE '%xxxxxx%' OR
        pe.DF_CP LIKE '%xxxxxx%'  OR
        pe.DF_Localidad LIKE '%xxxxxx%'  OR
        pe.DF_Provincia LIKE '%xxxxxx%'   OR
        pe.DF_Pais LIKE '%xxxxxx%'   OR
        pe.DE_Nombre LIKE '%xxxxxx%'    OR
        pe.DE_Direc1 LIKE '%xxxxxx%'    OR
        pe.DE_Direc2 LIKE '%xxxxxx%'    OR
        pe.DE_CP LIKE '%xxxxxx%'    OR
        pe.DE_Localidad LIKE '%xxxxxx%'   OR
        pe.DE_Provincia LIKE '%xxxxxx%'  OR
        pe.DE_Pais LIKE '%xxxxxx%'
        )
 

Genero la cadena Where a través de esta condición:

Editado: Código de programacion no permitido en foros de Bases de Datos. Leer las normas del for, por favor.

Está pendiente de depurar y reducir, pero funciona, al final el Where se guarda en una variable de sesión.

Y con el siguiente código creo el recordset con la página que quiera:

Editado: Código de programacion no permitido en foros de Bases de Datos. Leer las normas del for, por favor.

La función CrearRs_Paginado_MySQL la tenéis más arriba.

He incluido código asp, sé que no es el foro adecuado, si no creéis conveniente, borradlo o mover el post de sitio. Gracias por vuestra ayuda e interés.