Question 20252 in Mysql Question posted in
The official MySQL documentation can be found here.

Return already read and unread rows in mysql

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

Tags:

2

Answers


  1. 0

    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;

    or

    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;
    Login or Signup to reply.
  2. 0

    You can do it using LEFT JOIN to join your tables, then use GROUP BY and MAX CASE WHEN to get your desired output as follows :

    WITH cte as (
  SELECT f.id, f.Titulo, lf.Colaborador, lf.leitura 
  FROM Faq f
  LEFT JOIN LeituraFaq lf ON lf.Id_Faq = f.id
)
select id, MAX(Titulo), MAX(CASE WHEN Colaborador = 19 THEN Colaborador END) AS Colaborador,
max(CASE WHEN Colaborador = 19 THEN leitura END) AS leitura
from cte
group by id

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search