Complicated MySQL query between multiple tables

Question

Complicated MySQL query between multiple tables

Navigation

#1 by (0 votes)

1

Greetings!

The Query is about users, comments and the action on the comments (LIKE or DISLIKE). I try, but I do not know how to make the following return:

comment data, user data, LIKE total, DISLIKE total, action action: this will be of the user who has logged in.

The structure of the tables are the following:

user: id, etc ...
Comment: id, id_user, id_publication
action: id, comment_id , user_id, type (LIKE or DISLIKE)

I've tried the following partial queries that work:

SELECT * FROM comentario INNER JOIN usuario ON comentario.id_usuario = usuario.id WHERE comentario.id_publicacion = XXXXX
SELECT COUNT(*), tipo FROM accion WHERE id_comentario = XXXXX GROUP BY tipo

I'm not very good with SQL, thanks in advance.

mysql sql mysqli

asked by Islam Linarez 28.09.2017 в 01:39

source

1 answer

.prop ("checked", false) does not work Copy and replace a table from one instance sql to another [closed]

score 0 · Accepted Answer

Regards

I personally will use subqueries for my suggestions; starting with table comentario as root:

SELECT ccm.*, CONTEOS.SONLIKES, CONTEOS.SONDISLIKES, USR.*
FROM comentario  ccm
INNER JOIN 
(
    SELECT ID_COMENTARIO, ID_USUARIO, SUM(IIF(TIPO='LIKE', 1,0)) SONLIKES,
         SUM(IIF(TIPO='DISLIKE', 1,0)) SONDISLIKES
    FROM ACCION
    GROUP BY ID_COMENTARIO, ID_USUARIO
) CONTEOS ON CONTEOS.ID_COMENTARIO=CCM.ID AND CONTEOS.ID_USUARIO=CCM.ID_USUARIO
INNER JOIN USUARIO USR ON USR.ID=CCM.ID_USUARIO

To the above you must add conditional to filter only the user's data in session.

What I put as TIPO='LIKE' and TIPO='DISLIKE' adecualo to put the way it is identified when it is LIKE and when DISLIKE; as corresponds correctly (if it's number, letter, string, ....)