SQLite with SQL View and IS NULL


I have two tables:

**Tabla 1**
con los campos
cod_mensaje, cod_usuario, texto

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?

asked by Antonio 18.11.2018 в 10:37

0 answers