COUNT and complex query with INNER JOIN. (sql)

Question

COUNT and complex query with INNER JOIN. (sql)

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the comment tables, user, spam.

The business logic, is that simple: a user can make comments, and this comments can be marked as spam by other users.

The query I want to make must have the following structure:

datos de comentarios / datos de usuario quien comentó / "usuario_spam" / spam_total

smash_user: will be NULL if I have not marked the comment as spam, or it will be my "id" otherwise.

spam_total: The number of times the comment was marked as spam.

The SQL query I have made is the following:

SELECT 
   comentario.*,
   usuario.*,
   US.id AS usuario_spam, 
   COUNT(spam.id) AS spam_count

FROM comentario 

LEFT JOIN spam ON comentario.id = spam.id_comentario
INNER JOIN usuario ON comentario.id_usuario = usuario.id
LEFT JOIN usuario US ON US.id = spam.id_usuario = 123456 //ejemplo

WHERE
comentario.id_articulo = 11111 //ejemplo

GROUP BY comentario.id
ORDER BY comentario.fecha_creado DESC

Apparently the query between comments and know how much spam you have is, simple, the complicated is when I try to know if the user who enters (123456) has marked the comment as spam.

Example:

-----Usuario--       comentario-      ------spam-------
|____ id ____|    | id__ id_usu.|   |_ id __|__ id_comentario_|__id_usu.._|
|____  1 ____|    | 1 __|  1 ___|   |_ 1 ___|_________1_______|__  3 _____|       
|____  2 ____|    | 2 __|  1 ___|   |_ 2 ___|_________1_______|__  2 _____|
|____  3 ____|    | 3 __|  2 ___|   
                  | 4 __|  2 ___|

Assuming that the query is made by the user of id = 1. The resulting ones would be:

|__ comentario ____|____ usuario ____| ______ usuario spam ___| total spam |
|______ 1 _________|_____   1   _____| ________ NULL _________|_____ 2 ____|
|______ 2 _________|_____   1  ______| ________ NULL _________|_____ 0 ____|
|______ 3 _________|_______ 2 _______| ________ NULL _________|______0_____|
|______ 4 _________|_______ 2 _______| ________ NULL _________|______0_____|

Assuming that the query is made by the user of id = 3. The resulting ones would be:

|__ comentario ____|____ usuario ____| ______ usuario spam ___| total spam |
|______ 1 _________|_____   1   _____| ________ 3    _________|_____ 2 ____|
|______ 2 _________|_____   1  ______| ________ NULL _________|_____ 0 ____|
|______ 3 _________|_______ 2 _______| ________ NULL _________|______0_____|
|______ 4 _________|_______ 2 _______| ________ NULL _________|______0_____|

mysql sql

asked by Islam Linarez 07.04.2017 в 22:08

source

2 answers

0

If I do not understand what you're trying to do, it's the following:

  SELECT 
       comentario.*,
       usuario.*,
       US.id AS usuario_spam, 
       COUNT(spam.id) AS spam_count

    FROM comentario 

    LEFT JOIN spam ON comentario.id = spam.id_comentario
    INNER JOIN usuario ON comentario.id_usuario = usuario.id
    LEFT JOIN usuario US ON US.id = spam.id_usuario = usuario.id

    WHERE
    comentario.id_articulo = 11111 //ejemplo
    us.id = 123456 

    GROUP BY comentario.id
    ORDER BY comentario.fecha_creado DESC

answered by 07.04.2017 в 22:31

Problem with RecyclerView and List on Android c # pass data from one class to another

score 1 · Accepted Answer

There is no need to do an additional join with the usuario table to be able to determine if the user who enters has marked a comment as spam. It's just a matter of using a conditional aggregation function (like MAX ):

select c.*,
       u.*,
       max(case when s.id_usuario = 123456 then s.id_usuario end) as usuario_spam,
       count(s.id) as spam_count
  from comentario c
  join usuario u
    on u.id = c.id_usuario
  left join spam s
    on s.id_comentario = c.id
 where c.id_articulo = 11111
 group by c.id
 order by c.fecha_creado desc;

Demo