CODEWITHSUNDEEP
mysqlsql
morha13
morha13

Reputation: 1937

INNER JOIN causes duplicated rows

I'm trying to create a query that show me all the time differences (TIMEDIFF) in seconds.I want it to happen where there are no ID matches between 2 tables. here is my code, it will show what I'm trying to do better:

SELECT SUM(TIME_TO_SEC((TIMEDIFF(t.end,t.start)))) 
FROM times AS t 
INNER JOIN changes AS c 
ON t.id != c.shiftid 
WHERE t.deleted=0 AND t.userid=8

times:

------
Column       Type       Null     Default
------
//**id**//   int(11)    No   
userid       int(11)    No   
start        datetime   No   
end          datetime   No   
deleted      int(11)    No   

id    userID    start                 end                   deleted
 1     39       2014-12-13 12:52:05   2014-12-13 12:52:27   0
 2      8       2014-12-17 08:27:53   2014-12-17 13:29:55   1
 3     13       2014-12-18 16:22:09   2014-12-18 21:06:23   1
 4      8       2014-12-26 09:16:47   2014-12-26 15:34:40   1
 5      8       2012-02-29 09:10:00   2014-12-26 11:39:17   1
 6      8       2014-12-27 14:39:10   2014-12-27 14:40:09   1
 7      8       2014-12-25 14:44:26   2014-12-25 17:45:01   1
 8      8       2014-12-27 14:47:26   2014-12-27 14:48:17   1
 9     13       2014-12-27 16:37:13   2014-12-27 16:37:14   0
10      8       2014-12-27 21:01:58   2014-12-27 21:01:59   1
11     14       2014-12-27 21:02:00   2014-12-27 21:02:04   0
12      9       2014-12-27 21:02:00   2014-12-27 21:02:01   0
13     39       2015-01-02 18:18:41   2015-01-02 18:18:50   0
14      8       2015-02-15 15:13:23   2015-02-15 19:13:25   1
15      8       2015-02-19 14:22:12   2015-02-19 14:22:30   0
16      8       2015-02-19 14:27:01   2015-02-19 14:29:38   0
17      8       2015-02-19 12:33:25   2015-02-19 14:34:36   0
18      8       2015-02-19 14:38:10   2015-02-19 14:52:05   0
19      8       2015-02-19 16:09:07   2015-02-19 16:10:01   0

changes:

------
Column       Type       Null     Default
------
//**id**//   int(11)    No   
shiftid      int(11)    No   
userid       int(11)    No   
start        datetime   No   
end          datetime   No   
deleted      int(11)    No   

ID    shiftID    userID    start                  end                    deleted
 9    17         8         2015-02-19 11:33:25    2015-02-19 14:34:36    0
10    19         8         2015-02-19 16:05:07    2015-02-19 16:10:01    0
12    19         8         2015-02-19 16:04:07    2015-02-19 16:10:01    0

(if there is a better way of showing the tables please tell me) EDIT: this is a proper way to format tables.

Here , I want to display sum of difference between start and end from times table whose userid=8 and deleted=0 but times.id not present in changes.shiftid

Upvotes: 0

Views: 93

Answers (1)

Nagendra Nigade
Nagendra Nigade

Reputation: 876

We can also use sub-query here ,

select SUM(TIME_TO_SEC((TIMEDIFF(t.end,t.start)))) from times AS t WHERE userid=8 AND deleted=0 AND t.id not in (select shiftID from changes);

Upvotes: 1

Related Questions