MySql : Get data from two tables

Question

I am stuck with mysql query.Not able to proceed.

I have 2 tables where user's login time is recorded.Login time should be considered when either of the table contains entry. I want to find userwise sum of logins for a month.

I could reach till here. But not able to understand how to get sum

select table1.employeeId , date(table1.loginTime) as date1 , date(table2.loginTime) as date2 from table1 inner join table2 on table1.employeeId=table2.employeeId and table1.loginTime>='2017-01-01 00:00:00' and table1.loginTime<='2017-01-31 23:59:59' and table2.loginTime>='2017-01-01 00:00:00' and table2.loginTime<='2017-01-31 23:59:59'

For ex : count=0 employe1 logged
on 1-Jan-2017 in table1 & table2 <- count++ (if he logs in 2 tables then only 1 count should be considered)

on 2-Jan-2017 in table1 <- count++

So for employee1 count is 2

If it's for the same month then your expected output is wrong.... — Hackerman
– Hackerman, Commented Feb 7, 2017 at 18:17

T Gray · Accepted Answer · 2017-02-07 18:22:35Z

You could do this with outer joins, but it would be needlessly complex:

select employeeId , count(*) as loginCount from ( select employeeId , loginTime from table1 where loginTime between '2017-01-01 00:00:00' and '2017-01-31 23:59:59' union select employeeId , loginTime from table2 where loginTime between '2017-01-01 00:00:00' and '2017-01-31 23:59:59' ( as a group by employeeId;

Hackerman · Accepted Answer · 2017-02-07 18:43:28Z

Another approach:

 SELECT employeeId, COUNT(1)+ ( SELECT COUNT(1) FROM TABLE2 T2 WHERE MONTH(T2.loginTime) = 1 AND YEAR(T2.loginTime) = 2017 AND T2.employeeId = T1.employeeId ) AS LOGINTIMES FROM TABLE1 T1 WHERE MONTH(loginTime) = 1 AND YEAR(loginTime) = 2017 GROUP BY employeeId

Sample fiddle: http://sqlfiddle.com/#!9/45ce27/3/0

What if month is feb or year is 2018? solution shouldn't be generic?
The accepted solution isn't generic either. But both answers can be converted into a stored procedures; mine could accept month and year as parameters, the accepted answer two dates(begin of month and end of month). But that escapes the scope of the question.

Collectives™ on Stack Overflow

MySql : Get data from two tables

2 Answers 2

Comments

Sample fiddle: http://sqlfiddle.com/#!9/45ce27/3/0

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Sample fiddle: http://sqlfiddle.com/#!9/45ce27/3/0

2 Comments

Related