Problem in consulting BD with related tables

Question

Problem in consulting BD with related tables

Navigation

#1 by (7 votes)

0

Good morning. I have two tables, sessions and users.

A field in the sessions table is related to a field in the users table (the "user" field in both).

In the "users" table there will be users that will not have any value in the "num_access" field of the sessions table, so what I want is to show the entire "users" table together with the "num_access" field of the other table, even if there are users who do not have this field.

I make this query:

SELECT 
    SUM(sesiones.num_accesos) as num_accesos, 
    usuarios.usuario, 
    usuarios.nombre, 
    usuarios.centro 
FROM sesiones 
INNER JOIN usuarios ON usuarios.usuario = sesiones.usuario 
GROUP BY usuarios.usuario

And it shows me only the users that have value in the "num_accesses" field, that is, it only shows me the users that are present in the "sessions" table.

How can I show the "users" table even if they have the "num_access" field blank?

I have tried to change the table in the FROM but it does not work either.

Greetings.

mysql select

asked by M. Giner 24.10.2017 в 09:39

source

1 answer

select multiple tables in mysql Underlined blue links is not removed [closed]

score 7 · Accepted Answer

Have you tried to invert the order of the tables in the query?

You ask for ALL the users, and you recover the session information only from those who have it.

SELECT 
    SUM(sesiones.num_accesos) as num_accesos, 
    usuarios.usuario, 
    usuarios.nombre, 
    usuarios.centro 
FROM usuarios 
    LEFT JOIN 
        sesiones 
    ON usuarios.usuario = sesiones.usuario GROUP BY usuarios.usuario