[SOLUCIONADO] regresar el ultimo registro de cada cliente

[Palutin]

tengo dos tablas
clientes

pagos

estas dos tablas, las tengo unidas con un codigo de cliente, el campo se llama "codigo" en las dos tablas, lo que quiero es obtener en un listadoo todos los ultimos registros de cada cliente, tengo esta consulta ahora mismo

Código MySQL:

SELECT c.*, p.* 
FROM clientes c 
INNER JOIN pagos p ON c.codigo = p.codigo
WHERE c.sector='".$sector."'
GROUP BY p.codigo 
ORDER BY direccion
 

aqui lo que me devuelve es el primer registro de cada uno

si alguien me puede ayudar muchisimas gracias desde ya

[Libras]

podrias usar un subquery con un max, pero no dices los demas campos de tu tabla pagos, si tienes una fecha o un numero de pago, supongamos que tienes un numero de pago y codigo de cliente :) quedaria algo como esto:

Código MySQL:

SELECT c.*, p.* FROM clientes c 
INNER JOIN (select max(num_pago) as pago, codigo from pagos group by codigo) as p
ON c.codigo = p.codigo 
WHERE c.sector='".$sector."' GROUP BY p.codigo ORDER BY direccion
 

con esto te regresaria el ultimo registro de pagos(tomando en cuenta el numero de pago)

[Palutin]

Gracias por tu repuesta

de hecho si tengo un numero de confirmacion de pago, ya lo hice asi

la consulta me quedoo

Código SQL:

SELECT c.*, p.* FROM clientes c 
    INNER JOIN (SELECT MAX(confirmacionpago) AS confirmacionpago, codigo, mes_pago, fecha_pago, cantidad, correlativo 
    FROM pagos GROUP BY codigo) AS p ON c.codigo = p.codigo 
WHERE c.sector='".$sector."' 
GROUP BY p.codigo 
ORDER BY direccion
 

pero por algun motivo me sigue regresando el primer registro nada mas, confirmacion de pago es un auto increment

[Libras]

este esto no se puede hacer:

Código MySQL:

SELECT max(confirmacionpago) AS confirmacionpago, codigo, mes_pago, fecha_pago, cantidad, correlativo
from pagos group by codigo
 

te deberia de regresar un error porque en toda consulta que usa un group by los ccampos del select deben de estar incluidos en el group by.....

[gnzsoloyo]

Cuidado que MySQL tiene un comportamiento especial: Pueden listarse en el GROUP BY sólo parte de los campos y no todos, e incluso permite agrupar por campos no listados en el SELECT, pero que pertenezcan a una de las tablas.

Es un comportamiento específico de MySQL.

Ver: https://dev.mysql.com/doc/refman/5.6...-handling.html

[Libras]

Iniciado por gnzsoloyo

Cuidado que MySQL tiene un comportamiento especial: Pueden listarse en el GROUP BY sólo parte de los campos y no todos, e incluso permite agrupar por campos no listados en el SELECT, pero que pertenezcan a una de las tablas.

Es un comportamiento específico de MySQL.

:O eso no lo sabia, con razon le sigue dando solo un registro sin marcar error :P

[gnzsoloyo]

Sin duda es por eso.
El tema es que para que devuelva el maximo de un set de casos, el GROUP BY debe ser más discriminante. No alcanza con el "codigo" para devolver el mayor valor de algo.
Tiene que agrupar también algun discriminante adicional, como mínimo. Eso, sin olvidarse que si para un sector dado hay un único cliente, seguirá devolviendo un único registro.
Sin ver los datos reales es imposible saber si la respuesta de la consulta es correcta o no.

[Palutin]

entonces lo que tengo que hacer es agregar algun otro filtro en la consulta?

Código SQL:

SELECT c.*, p.* FROM clientes c 
        INNER JOIN (SELECT MAX(confirmacionpago) AS pago, codigo, mes_pago, fecha_pago, cantidad, correlativo
        FROM pagos) AS p ON c.codigo = p.codigo 
        WHERE c.sector='".$sector."' GROUP BY p.codigo ORDER BY direccion
 

esto es lo que tengo, trate de agregar o quitar algunas cosas pero no me ha funcionado

voy a dejar aqui la estructura de las dos tablas si sirve de algo

clientes
codigo int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(codigo),
nombre char(50) NOT NULL,
sector char(50) NOT NULL,
direccion char(80) NOT NULL,
comentario char(200),
nit char(10) NOT NULL,
fechacreacion date,
estado char(15) NOT NULL,
telefono char(20),
correo char(30),

pagos

confirmacionpago int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(confirmacionpago),
codigo int NOT NULL,
idbuscador int,
FOREIGN KEY(codigo) REFERENCES clientes(codigo),
fecha_pago date NOT NULL,
mes_pago char(20) NOT NULL,
correlativo char(30) NOT NULL,
cantidad int,
comentario char(30),
utc int,

le habia agregado un utc a la tabla de pagos tratando de usarlo como un MAX para conseguir los ultimos registros pero hasta ahora todos mis intentos has sido fallidos

[Libras]

podrias poner un ejemplo de tus datos para poder ayudarte un poco mejor? no la estructura de tus tablas eso no ayuda en este momento

[Libras]

Iniciado por gnzsoloyo

Sin duda es por eso.
El tema es que para que devuelva el maximo de un set de casos, el GROUP BY debe ser más discriminante. No alcanza con el "codigo" para devolver el mayor valor de algo.
Tiene que agrupar también algun discriminante adicional, como mínimo. Eso, sin olvidarse que si para un sector dado hay un único cliente, seguirá devolviendo un único registro.
Sin ver los datos reales es imposible saber si la respuesta de la consulta es correcta o no.

Y tambien pense en eso que con la poca informacion que da el qu epregunta es dificil saber si la consulta funcione o no, ya que no se sabe si el discriminante usado en la agrupacion es el que de verdad funcione :P

[Palutin]

por ejemplo, como estoy usando INNER JOIN entonces son valores de de las dos tablas, esto es lo que quiero que se imprima:

Codigo 10001
Nombre cliente 1
Direccion 3 calle 2-20
Ultimo Mes Pagado Enero
Fecha de Pago 2015-07-26
Documento R3838383838
Cantidad Pagada 60
Telefono 3434343434
Estado activo

Ya le ingrese pagos hasta mayo y solo me muestra enero

[Libras]

Un ejemplo de los datos de las 2 tablas inplicadas, podrias usar la fecha y como bien te dijeron una agregacion no necesariamente tiene que llevar todos los campos en el select, prueba con esto:

Código MySQL:

SELECT c.*, p.* FROM clientes c 
INNER JOIN (SELECT MAX(fecha_pago) AS pago, codigo
FROM pagos
group by codigo
) as p ON c.codigo = p.codigo 
WHERE c.sector='".$sector."' GROUP BY p.codigo ORDER BY direccion
 

[Palutin]

aqui en lo que quiero imprimir van datos de las dos tablas

Codigo 10001
Nombre cliente 1
Direccion 3 calle 2-20
Ultimo Mes Pagado Enero
Fecha de Pago 2015-07-26
Documento R3838383838
Cantidad Pagada 60
Telefono 3434343434
Estado activo

pero al momento de poner el MAX y no detallar los otros campos no los reconoce, los detallo igualmete y me sigue mostrando el primer registro, no se si se podria evitar con otra estructura de tablas o algun otro aspecto?...

llevo al rededor de 3 dias con el mismo problema, es lo unico que me falta para terminar el proyecto

[Libras]

Iniciado por Palutin

aqui en lo que quiero imprimir van datos de las dos tablas

Codigo 10001
Nombre cliente 1
Direccion 3 calle 2-20
Ultimo Mes Pagado Enero
Fecha de Pago 2015-07-26
Documento R3838383838
Cantidad Pagada 60
Telefono 3434343434
Estado activo

pero al momento de poner el MAX y no detallar los otros campos no los reconoce, los detallo igualmete y me sigue mostrando el primer registro, no se si se podria evitar con otra estructura de tablas o algun otro aspecto?...

llevo al rededor de 3 dias con el mismo problema, es lo unico que me falta para terminar el proyecto

Esos datos no me sirven, ocupo los datos de las 2 tablas, y claro que no te va a detallar los demas campos, lo que queria ver es si con esa consulta obtienes el registro que ocupas, si lo obtienes ya se podria ver como añadir los demas datos que necesitas, y como te digo ocupo los datos de cada una de las tablas, para ver como estas haciendo la relacion

[Palutin]

clientes

codigo 10001
nombre cliente1
sector Barberos
direccion 3 calle 2-20
estado Acivo
telefono 3434343434
correo [email protected]

pagos

confirmacionpago 53000
codigo 10001
fechapago 2015-07-26
mes_pago Enero
Correlativo R3838383838
cantidad 60


esos son los datos de cada tabla, en el listado que quiero generar van datos de las dos, son los que habia puesto anteriormente

[gnzsoloyo]

Bueno, con eso que posteas no podemos hacer una prueba eficiente, porque estás poniéndonos un sólo registro de cada una de las tablas, con lo que la query seguirá devolviendo un único registro, y en este caso el exacto...

Trata de entender el problema: No podemos hacer pruebas con tu estructura de datos si no posteas los CREATE TABLE de las tablas, y una sucesión de INSERT para cada tabla, de modo que tengamos modo de replicar el problema.
¿Se entiende?

Si quieres hacerlo simple, puedes hacer un backup de la base y postear aquí sólo lo que te indico.


Como nota, esto NO es un CREATE TABLE:

clientes
codigo int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(codigo),
nombre char(50) NOT NULL,
sector char(50) NOT NULL,
direccion char(80) NOT NULL,
comentario char(200),
nit char(10) NOT NULL,
fechacreacion date,
estado char(15) NOT NULL,
telefono char(20),
correo char(30),

Es un pedazo de la tabla, sin el formato adecuado, y no vamos a poner a recrear la sentencia que bien puedes pasar con un backup, o bien ejecutando esto para recuperarla:

Código MySQL:

SHOW CREATE TABLE clientes;
 

Código MySQL:

SHOW CREATE TABLE pagos;
 

[gnzsoloyo]

Volvamos al principio, porque mirando tu planteo me da la impresión de que hay un error de comprensión desde el inicio...

Iniciado por Palutin

tengo dos tablas
clientes

pagos

estas dos tablas, las tengo unidas con un codigo de cliente, el campo se llama "codigo" en las dos tablas, lo que quiero es obtener en un listadoo todos los ultimos registros de cada cliente, (...)


(...)

De acuerdo a este planteo, me da la impresión de que lo que quieres es un listado completo, de todos los pagos de cada cliente, ordenados en del más reciente al mas antiguo. No estás diciendo el último, sino los últimos
¿Es así?

Porque si eso es lo que quieres, el GROUP BY no tiene ningún sentido, ya que n o necesitas un agrupamiento para obtener sumatorias, sino un agrupamiento visual, y eso es un ORDER BY.
Por eso digo que me parece que hay un error de entendimiento de parte tuya de lo que significa realmente esa cláusula...

¿Es correcto esto que digo? ¿Es agrumar la lista lo que quieres?

[Palutin]

Ok, creo que al principio di muy poca informacion, voy a tratar de detallar al maximo mi situacion, si necesitas mas informacion solo hacemelo saber, y muchas gracias por las respuestas y por la ayuda.

estoy trabajando en un proyecto, es php, css y mysql lo que he estado usando, el programa tiene la capacidad de crear clientes y registrar pagos, hay una seccion donde generas un listado de un sector especifico, en este es donde tengo el problema con la consulta.

en esta pagina se selecciona sector, Estado (Activo, inactivo o ambos) y Documento(Recibos, Facturas o ambos).

Voy a dejar el CREATE TABLE que use en el documento de instalacion

CLIENTES:

Código MySQL:

CREATE TABLE clientes(
    codigo int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(codigo),
    nombre char(50) NOT NULL,
    sector char(50) NOT NULL,
    direccion char(80) NOT NULL,
    comentario char(200),
    nit char(10) NOT NULL,
    fechacreacion date,
    estado char(15) NOT NULL,
    telefono char(20),
    correo char(30),
    usuario char(25)
)AUTO_INCREMENT = 10001
 

el campo de Usuario es para que se registre el usuario que realizo el registro, asi que es solo para uso interno.

PAGOS:

Código MySQL:

CREATE TABLE pagos(
    confirmacionpago int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(confirmacionpago),
    codigo int NOT NULL,
    FOREIGN KEY(codigo) REFERENCES clientes(codigo),
    fecha_pago date NOT NULL,
    mes_pago char(20) NOT NULL,
    correlativo char(30) NOT NULL,
    cantidad int,
    comentario char(30),
    utc int,
    usuario char(25) NOT NULL
)AUTO_INCREMENT = 53000
 

Esos son los CREATE TABLE de clientes y pagos

a continuacion, voy a colocar lo que necesito imprimir(los cuales son campos de ambas tablas) y como tengo la consulta actualmente.

Codigo
Nombre
Direccion
Ultimo Mes pagado
Fecha de pago
Documento
Cantidad Pagada
telefono
Estado

Esta es la consulta:

Código MySQL:

SELECT c.*, p.* 
FROM clientes c INNER JOIN (SELECT MAX(fecha_pago) AS pago, codigo, mes_pago FROM pagos group by codigo) as p ON c.codigo = p.codigo 
WHERE c.sector='".$sector."' GROUP BY p.codigo ORDER BY direccion
 

me esta regresando unicamente el primer registro, yo necesito el ultimo, igualmente voy a poner ejemplo de los registros, no tengo un listado de clientes entonces he estado poniendo datos de pruebas, pongo 2 o 3 registros de clientes y le agrego varios pagos a cada uno.

Clientes

codigo 10001 10002
nombre cliente 1 Cliente 2
Sector Barberos Barberos
direccion 3 calle 2-20 8 calle 10-5
comentario comentario comentario
NIT 2234234234 465774589
Fecha creacion 2015-07-26 2015-07-27
Estado Activo activo
Teleono 34343434 555555555
Correo [email protected] [email protected]
Usuario test test

Pagos

Confirmacionpago 53000 53001 53002 53003
codigo 10001 10001 10002 10002
fecha_pago 2015-07-26 2015-07-26 2015-07-26 2015-07-26
Mes_pago Enero Febrero Enero febrero
correlativo R234234223 F0980980980 R23847298 R29837928
cantidad 60 60 60 60
comentario comentario comentario comentario comentario
utc 1437954671 1437955043 1437989029 1437989839
Usuario test test test test


ahi esta todos los datos creo que seran utiles, y muchisimas gracias por la ayuda

igualmente si falta algo hacemelo saber por favor

[Palutin]

no, tengo un documento que ya hace eso y funciona a la perfeccion, lo que necesito es un registro por cada cliente, con el ultimo ppago que hizo cada cliente

[gnzsoloyo]

Iniciado por Palutin

no, tengo un documento que ya hace eso y funciona a la perfeccion, lo que necesito es un registro por cada cliente, con el ultimo ppago que hizo cada cliente

No se entiende ni a que te refieres, ni tampoco a quién le sestás contestando.

Por favor, trata de ser más específico.

¿Te resulta muy dificil poner los INSERT puros?

Confirmacionpago 53000 53001 53002 53003
codigo 10001 10001 10002 10002
fecha_pago 2015-07-26 2015-07-26 2015-07-26 2015-07-26
Mes_pago Enero Febrero Enero febrero
correlativo R234234223 F0980980980 R23847298 R29837928
cantidad 60 60 60 60
comentario comentario comentario comentario comentario
utc 1437954671 1437955043 1437989029 1437989839
Usuario test test test test

Resulta engorroso tomar esa cosa que posteas y separar los registros uno a uno para realizar una replica del problema.


Es por eso que te sugerí que hagas un backup. En un Backup puedes copiar los INSERT que se generan en forma automática en el script de respaldo, y pegarlos aquí.

Por otro lado, POR FAVOR, usa los Highlight que tienes disponibles en el área de edición del mensaje.
Están para postear codigos de diverso tipo de una forma legible...

[Palutin]

esta bien, dame nada mas un momento voy a hacer eso, voy a extraer los insert de los registros, y no habia usado los highlight, soy nuevo aqui y no lo habia notado.
igualmente gracias por la informacion

en un momento pondre aqui los insert de estos registros

[Palutin]

Aqui los insert en la tabla CLIENTES:

cliente 1

Código MySQL:

INSERT INTO `clientes`(`codigo`, `nombre`, `sector`, `direccion`, `comentario`, `nit`, `fechacreacion`, `estado`, `telefono`, `correo`, `usuario`) VALUES (10001,'cliente 1','Barberos','3 calle 2-20','comentario','23233323','2015-07-26','activo','2323343543','[email protected]','test')
 

y el cliente 2

Código MySQL:

INSERT INTO `clientes`(`codigo`, `nombre`, `sector`, `direccion`, `comentario`, `nit`, `fechacreacion`, `estado`, `telefono`, `correo`, `usuario`) VALUES ('10002','cliente 2','Barberos','8 calle 10-5','comentario','55555555','2015-07-27','activo','34443443','[email protected]','test')
 

ahora los insert en la tabla PAGOS: (2 por cada cliente)

cliente 1

Código MySQL:

INSERT INTO `pagos`(`confirmacionpago`, `codigo`, `fecha_pago`, `mes_pago`, `correlativo`, `cantidad`, `comentario`, `utc`, `usuario`) VALUES ('53000','10001','2015-07-26','Enero','R383898493','60','comentario','1437954671','test')
 

Código MySQL:

INSERT INTO `pagos`(`confirmacionpago`, `codigo`, `fecha_pago`, `mes_pago`, `correlativo`, `cantidad`, `comentario`, `utc`, `usuario`) VALUES ('53001','10001','2015-07-26','Febrero','R1234849','60','comentario','1437955043','test')
 

cliente 2

Código MySQL:

INSERT INTO `pagos`(`confirmacionpago`, `codigo`, `fecha_pago`, `mes_pago`, `correlativo`, `cantidad`, `comentario`, `utc`, `usuario`) VALUES ('53002','10002','2015-07-27','Enero','R9876549','60','comentario','1437989029','test')
 

Código MySQL:

INSERT INTO `pagos`(`confirmacionpago`, `codigo`, `fecha_pago`, `mes_pago`, `correlativo`, `cantidad`, `comentario`, `utc`, `usuario`) VALUES ('53003','10002','2015-07-27','Febrero','R29837928','60','comentario','1437989839','test')
 

Ahi estan, 2 clientes, 2 pagos por cada uno, Enero y Febrero

en este caso lo que necesito es listarlos donde solo se muestre el ultimo pago hecho, en este caso solo febrero.

espero que no hayan mas confusiones con esto, igualmente haceme saber cualquier inconveniente

Muchas Gracias

[Libras]

Código SQL:

CREATE TABLE #clientes(
codigo INT,
nombre VARCHAR(20)
)
 
 
CREATE TABLE #pagos(
confirmacionpago INT,
codigo INT,
fecha_pago datetime,
mes_pago INT,
correlativo VARCHAR(20),
cantidad INT,
comentario VARCHAR(20),
utc VARCHAR(20),
usuario VARCHAR(20)
)
 
CREATE TABLE #meses(
id_mes INT,
mes VARCHAR(20)
)
 
INSERT INTO #meses VALUES (1,'Enero')
INSERT INTO #meses VALUES (2,'Febrero')
 
INSERT INTO #clientes(codigo, nombre) VALUES (10001,'cliente 1')
INSERT INTO #clientes(codigo, nombre) VALUES (10002,'cliente 2')
 
 
 
INSERT INTO #pagos(confirmacionpago, codigo, fecha_pago, mes_pago, correlativo, cantidad, comentario, utc, usuario) VALUES (53000,10001,'2015-07-26',1,'R383898493','60','comentario','1437954671','test')
INSERT INTO #pagos(confirmacionpago, codigo, fecha_pago, mes_pago, correlativo, cantidad, comentario, utc, usuario) VALUES (53001,10001,'2015-07-26',2,'R1234849','60','comentario','1437955043','test')
INSERT INTO #pagos(confirmacionpago, codigo, fecha_pago, mes_pago, correlativo, cantidad, comentario, utc, usuario) VALUES (53003,10002,'2015-07-27',2,'R29837928','60','comentario','1437989839','test')
INSERT INTO #pagos(confirmacionpago, codigo, fecha_pago, mes_pago, correlativo, cantidad, comentario, utc, usuario) VALUES (53002,10002,'2015-07-27',1,'R9876549','60','comentario','1437989029','test')
 
 
 
 
 
 
SELECT t1.codigo,t1.nombre,t3.confirmacionpago,t3.fecha_pago,t4.mes,t3.correlativo,t3.cantidad,t3.comentario,t3.utc,t3.usuario FROM #clientes AS t1
LEFT JOIN
(SELECT MAX(mes_pago) mes, codigo FROM #pagos GROUP BY codigo) AS t2 ON (t1.codigo=t2.codigo)
LEFT JOIN #pagos AS t3 ON (t2.codigo=t3.codigo AND t2.mes=t3.mes_pago)
LEFT JOIN #meses AS t4 ON (t4.id_mes=t3.mes_pago)
 

Resultado:
codigo nombre confirmacionpago fecha_pago mes correlativo cantidad comentario utc usuario
10001 cliente 1 53001 2015-07-26 00:00:00.000 Febrero R1234849 60 comentario 1437955043 test
10002 cliente 2 53003 2015-07-27 00:00:00.000 Febrero R29837928 60 comentario 1437989839 test

Sugerencia, para hacer lo que necesitas cambie el "mes" por un indicador numerico y lo relacione con una tabla "meses" para obtener el nombre, ademas te comento que la tabla pagos no estaba normalizada a 3 forma porque todavia estabas repitiendo los valores para mes y esos se pueden obtener agregando un catalogo como lo hice yo :).

P.D: La sentencia es en sql server(el create table con #, eso es cree una tabla temporal para hacer las pruebas, pero el query te puede funcionar en mysql)

[Palutin]

Muchas gracias! ya lo pude hacer funcionar con esa consulta, efectivamente agregue el catalogo de meses en una tabla.

Código MySQL:

SELECT t1.codigo,t1.nombre,t3.confirmacionpago,t3.fecha_pago,t4.mes,t3.correlativo,t3.cantidad,t3.comentario,t3.utc,t3.usuario FROM #clientes AS t1
LEFT JOIN
(SELECT MAX(mes_pago) mes, codigo FROM #pagos GROUP BY codigo) AS t2 ON (t1.codigo=t2.codigo)
LEFT JOIN #pagos AS t3 ON (t2.codigo=t3.codigo AND t2.mes=t3.mes_pago)
LEFT JOIN #meses AS t4 ON (t4.id_mes=t3.mes_pago)
 

[Libras]

Excelente :)