I have two tables:
**Tabla 1**
Mensajes
con los campos
cod_mensaje, cod_usuario, texto
**Tabla2**
Accesos
cod_mensaje, cod_usuario
In the access table, I record every time a user reads a record from the messages table.
I have created a view that contains the number of times a user reads a message:
select cod_mensaje, cod_usuario, count(*) as contador
from accesos
group by cod_mensaje, cod_usuario
The idea is to have a query with all the messages and the "counter" field so that you can see the number of accesses that each message has had:
select men.Cod_Mensaje, acc.Cod_Mensaje, acc.contador
from mensajes men
left join numaccesos acc ON (men.Cod_Mensaje = acc.cod_mensaje)
where men.cod_usuario = "708700449" or men.cod_usuario = ""
In this query, all user messages 708700449 and with the counter field that shows the number of accesses you have had or blank if you have not accessed that message. Up to that point everything works fine.
The problem comes if I want to include the field "counter" in this query, for example to show only the messages that have been read what I do is add at the end of that query:
AND contador IS NOT NULL
The results you give are not correct, it shows me a mixture of null records with records with value.
I've tried putting contador = "valor númerico"
, contador IS NULL
, ...
If I include the counter field in the query, the results are not correct.
Can anyone explain what happens?