Relacionar dos tablas (chat y accesos) comparando fechas

[X3mdesign]

Hola foro, me estoy pegando con una query que relacione dos tablas.

Por un lado, tengo una tabla `chat` que guarda el id de la sala, la fecha del mensaje y el usuario que lo realizó.

Código:

CREATE TABLE IF NOT EXISTS `chat` (
  `id` int(11) NOT NULL,
  `fecha_hora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id_usuario` int(11) NOT NULL,
  `id_sala` smallint(6) NOT NULL,
  `mensaje` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Y por otro lado tengo otra tabla en la que se guarda los accesos de los usuarios al ámbito de ese `chat, donde se guarda el acceso a la sala y si el usuairo ha abierto la ventana del chat.

Código:

CREATE TABLE IF NOT EXISTS `accesos` (
  `id` int(11) NOT NULL,
  `fecha_hora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id_usuario` smallint(6) NOT NULL,
  `id_sala` smallint(6) NOT NULL,
  `viendo` varchar(50) COLLATE latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Necesito listar las salas que tienen mensajes nuevos y el usuario no los ha leído... ojo, se podría dar el caso que el usuario no tuviera registro en accesos con viendo="chat"

He parido, esto, pero evidentemente, da error #1111 - Invalid use of group function0:

Código:

SELECT c.id_sala, MAX(a.fecha_hora), MAX(c.fecha_hora) 
   FROM chat c, accesos a 
   WHERE c.id_sala=a.id_sala AND a.id_usuario=1 AND a.viendo="chat" AND MAX(c.fecha_hora) > MAX(a.fecha_hora)

A la hora de hacer un SELECT con MAX(fecha_hora) no hay problema, pero si lo pongo en el WHERE es cuando casca...

¿Alguien que me pueda ayudar? Michas gracias!!!

[Libras]

Código MySQL:

select * from(
SELECT c.id_sala, MAX(a.fecha_hora) fecha1, MAX(c.fecha_hora) fecha2
   FROM chat c, accesos a 
   WHERE c.id_sala=a.id_sala AND a.id_usuario=1 AND a.viendo="chat"
) as t1 where fecha2 > fecha1
 

[X3mdesign]

Gracias Libras por tan pronta respuesta... pero no está funcionando, no se muy bien porqué porque no llego a comprender la query, pero tengo un listado de todas las salas que me muestra si hay mensajes sin leer de forma individual, y con tu query no me muestra ninguna, habiendo resultado positivo en el listado...

[Libras]

No es mi query, es tu query, yo solo lo que hice fue acomodarla para que te tomara los valores de max que estas usando en el where, si no te da los valores entonces revisa como estas haciendo los joins, otra cosa que no vi, usa joins explicitos en lugar de joins implicitos :)

[X3mdesign]

Ok, gracias por la recomendación de los JOINS, soy autodidacta en esto y aprendo a base de romper muros... acabo de ver cómo son los implícitos y que están obsoletos.... cambiaré mi sistema ;)

Seguiré en ello...

[Libras]

Ahora si requieres un poco mas de ayuda podrias poner un ejemplo de tus datos y que es lo que esperas obtener para entender mejor la problematica :)

[X3mdesign]

Hola de nuevo Libras y gracias ;)

No hay muchos más datos que dar, el usuario habitual puede acceder a unas salas de la aplicación donde tiene varias herramientas, una de ellas es el chat. El chat se lo encuentra oculto y debe pinchar sobre la ventana para poder leerlo.

En la tabla `chat`, se almacenan todos los mensajes de cada sala de chat. El id_sala viene referenciado porque no todos los usuarios pueden acceder a las mismas salas donde hay chat.

En la tabla `accesos` guardo la información de cada usuario de cuándo accede a la salas y ve las herramientas a su disposición.

Puesto que en el chat interviene más gente, quiero tener un botón donde se muestren las salas donde se han realizado comentarios y el usuario no los ha leído por que se han producido después de su último acceso.

Saludos!!!

[Libras]

Al pedir datos me referia a algo como esto:

tabla1
dato1
dato2

tabla2
dato1
dato2

Lo que espero:
tabla3
dato1, sumatoria(dato1) donde fecha sea esto y este agrupado por esto.....