Consult two or more columns of two or more tables in a single statement

Navigation
0

I have two tables Usuarios and Logs .

In the Logs table I have ID, fecha, hora, usuario, estado, estaciòn . In the table Usuarios I have ID, Empleado, Nombre, Apellido, permisos, ID_Campaña, horario .

I need to create a statement that queries from the Logs table the fields of fecha, hora , usuario, estado and that from the Users table I see the nombre, apellido .

The Logs table contains about 1500 to 2000 records per day, and multiple records of a single user.

I wanted to use a Inner join , but I do not get the result I want.

Here is my statement: 

SELECT 'Date' , 'Time' , 'UserName' as  'Employee' , 'Status' 
  FROM 'logs' 
  Inner  JOIN (SELECT 'FName' , 'LName' , 'Campaign_ID','Employee'  FROM 'users') users 
    ON 'UserName' = 'Employee' 
      WHERE 'logs'.'Date' = '1/11/2018' 
      And 'logs'.'Time' > '07:00:00' And 'logs'.'Time' < '08:00:00' 
      And 'logs'.'Status' Between 'Clocked In' 
      AND 'Clocked Out' order by 'logs'.'UserName'

Thanks for your time.

    
asked by Neoz Memphisto 14.01.2018 в 06:45
source

2 answers

0

Try this way: 

SELECT u.Nombre , u.Apellido, l.fecha, l.hora, l.usuario, l.estado from Usuarios u, Logs l where u.id = l.empleado and l.date between 'fecha_1' and 'fecha_2' and l.estado in ('Clocked In', 'Clocked Out')
    
answered by 14.01.2018 / 07:05
source
0

There are two ways to do it:

I understand that the user field of the log table is FK of the user table 

SELECT u.nombre, u.apellido, l.fecha, l.hora, l.usuario, l.estado FROM log l, usaurio u WHERE l.usuario=u.id;

The other one is with Inner join: 

SELECT u.nombre, u.apellido, l.fecha, l.hora, l.usuario, l.estado FROM log l INNER JOIN usuario u ON u.id=l.usuario;
    
answered by 14.01.2018 в 07:25
Open a modal login form with javascript from a different html file LocalStorage login with Phonegap