Count total equal records in two different tables with Group By among 3 related tables

Question

Count total equal records in two different tables with Group By among 3 related tables

Navigation

#1 by (0 votes)

0

I have tried to perform a query that extracts the information from a table where certain information is stored

This table contains comments

Additionally I have two other tables that are related to this, such as the table of likes and response table of comments, what I want and what I tried to do is to be able to get the number of answers and I like that they have each comment , what I tried to do with the following query

Schema of tables and query

Tabla - comentarios
id_comentarios int(11)
comentarios text

Tabla - respuestas
id_respuestas int(11)
id_comentarios int(11)
respuesta text

Tabla - Me_gusta
id_megusta int(11)
id_comentarios int(11)
id_user_likes int(11)

The comments table is related to the Me_guta table and the answers table

The query with which I try to get the information I want is this

SELECT com.id_comentarios as id_comentario, com.comentarios as comentarios, 
COUNT(lk.id_comentarios) AS count_megusta, COUNT(res.id_comentarios) as count_respuestas
FROM comentarios com 
LEFT JOIN l_like_foro as lk ON com.id_comentarios = lk.id_comentarios
LEFT JOIN respuestas as res ON res.id_comentarios = com.id_comentarios 
WHERE com.id_foro = 1 GROUP BY com.id_comentarios,lk.id_comentarios HAVING COUNT(lk.id_comentarios) >= 0

When I execute this query, the responses of each comment group them well, with the amounts that correspond to them, but when the query is grouped and the count is made to the likes I do not correspond to the amount

mysql sql

asked by Jhon Dember Murillo Mendez 01.08.2018 в 05:28

source

1 answer

Notifications on android studio Table relationships of the EF error model

score 0 · Answer 1

SELECT id_comentarios AS id,
    count(id_comentarios) AS cant,
    'respuesta' AS dato
    FROM respuestas
    GROUP BY id_comentarios

UNION
SELECT id_comentarios AS id,
    count(id_comentarios) AS cant,
    'me_gusta' AS dato
    FROM Me_gusta
    GROUP BY id_comentarios

ORDER BY id_comentarios;

Using this query you get 2 lines for each comment, for example the comment 1 has 5 answers and 3 I like:

--------------------------
| id | cant | dato       |
--------------------------
| 1  |  5   | comentario |
--------------------------
| 1  |  3   | me_gusta   |
--------------------------