Return already read and unread rows in mysql

March 30, 2023

I have the following tables, where in the first I have procedures and in the second I register the users who read them according to the click on the procedures:

CREATE TABLE Faq ( id INT, Titulo varchar(30) );
INSERT INTO Faq VALUES
(1, 'Teste'),
(2, 'Teste1'),
(3, 'Teste2'),
(4, 'Teste3'),
(5, 'Teste4');

CREATE TABLE LeituraFaq( id INT, Id_Faq INT, leitura INT, Colaborador INT );
INSERT INTO LeituraFaq VALUES
(1, 3, 1, 19),
(2, 5, 1, 19),
(1, 3, 1, 24),
(1, 2, 1, 24);

What I intend with the two tables is to understand the procedures that each one has already read and the procedures that still have to be read.

I have the following query that returns what each contributor has already read, like this:

SELECT Faq.id, Titulo, LeituraFaq.Colaborador, leitura 

FROM Faq LEFT OUTER JOIN LeituraFaq ON LeituraFaq.Id_Faq = Faq.id

WHERE LeituraFaq.Colaborador = '19'

In addition to the procedures already read, I also need the query to return the procedures that each user has not yet read.

The result I want for example for the user with id 19:

id  Titulo  Colaborador leitura
1   Teste
2   Teste1
3   Teste2    19           1
4   Teste3
5   Teste4    19           1

It means that the user with id 19 has already read two procedures and has 3 to read. This is the intended result. The result must be dynamic and per user.

Here is an example of what I have:

example

>Solution :

It seems that you need in either

SELECT t2.Colaborador, t1.Titulo
FROM Faq t1
CROSS JOIN (
    SELECT DISTINCT Colaborador
    FROM LeituraFaq
    ) t2
LEFT JOIN LeituraFaq t3 ON t3.Id_Faq = t1.id AND t3.Colaborador = t2.Colaborador
WHERE t3.id IS NULL
ORDER BY 1,2;

SELECT t2.Colaborador, GROUP_CONCAT(t1.Titulo)
FROM Faq t1
CROSS JOIN (
    SELECT DISTINCT Colaborador
    FROM LeituraFaq
    ) t2
LEFT JOIN LeituraFaq t3 ON t3.Id_Faq = t1.id AND t3.Colaborador = t2.Colaborador
WHERE t3.id IS NULL
GROUP BY 1
ORDER BY 1;

Explanation.

Subquery t2 gathers complete Colaborador list.

Cross join with t1 produces all possible pairs (Titulo-Colaborador).

LEFT JOIN t3 with further WHERE .. IS NULL selects all above pairs which have no according row in t3 table.

If the table which stores complete Colaborador list exists then you’d use it instead of subquery t2.

if it is possible to return read and unread messages in the same query, that would be ideal. – Bruno

SELECT t1.Titulo,
       COALESCE(t2.Colaborador, '') Colaborador,
       COALESCE(t2.leitura, '') leitura
FROM Faq t1
LEFT JOIN LeituraFaq t2 ON t2.Id_Faq = t1.id AND t2.Colaborador = 19
ORDER BY 1;