Help for this query

Asked

Viewed 38 times

1

Good afternoon Guys, I have these two tables that relate to know how many times a user accessed the system:

inserir a descrição da imagem aqui

And I have this query:

select nome_usuario, count(id_log) as qtde from logs as l left join usuario as u on l.usuario_log = u.id_usuario group by usuario_log order by qtde desc

Today it brings me the result of how many times the user accessed. And I would like that if not find table records log assign zero '0' as I do this?

mysql

  • you want to bring all users independent if you do not have in the log table.

  • 1

    That, and assigning 0.

  • I leave as a tip: When relating tables, use exactly the same name in the columns id, facilitates understanding and makes it possible to use the clause using in the query, which is very practical...

1 answer

1


In the SQL of the question, change LEFT JOIN for RIGHT JOIN

select u.id_usuario, u.nome_usuario, count(l.id_log) as qtde from logs as l 
        right join usuario as u 
           on l.usuario_log = u.id_usuario 
             group by u.id_usuario, nome_usuario 
              order by qtde desc

  • I tried this, but it doesn’t bring all the results. It brings only one record with value = 0. And there are many others.

  • You can put examples of values in your question!

  • 1

    Exactly. Thank you Friend.

  • @Eduardosantos takes a look so it would be better as last edits

  • 1

    It worked too. Thank you

  • or group by u.id_usuario, nome_usuario which is correct, I also did the editing.

Show 1 more comment

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.