mysql - Get a result by comparing two tables with an identical column -

mysql> select * on_connected; +----+-----------+-------------+---------------------------+---------------------+ | id | extension | destination | call_id                   | created_at          | +----+-----------+-------------+---------------------------+---------------------+ | 11 |   1111111 | 01155555551 | 521243ad953e-965inwuz1gku | 2013-08-19 17:11:53 | +----+-----------+-------------+---------------------------+---------------------+  mysql> select * on_disconnected; +----+-----------+-------------+---------------------------+---------------------+ | id | extension | destination | call_id                   | created_at          | +----+-----------+-------------+---------------------------+---------------------+ |  1 |   1111111 | 01155555551 | 521243ad953e-965inwuz1gku | 2013-08-19 17:11:57 | +----+-----------+-------------+---------------------------+---------------------+ 1 row in set (0.00 sec) 

there time difference of 4sec between two. calculate difference using query of type. i'm aware of timefiff() , joins lack skills form query @ point.

here's attempt far:

select timediff(to_seconds(od.created_at), to_seconds(oc.created_at))  on_connected oc  join on_disconnected od  on oc.call_id=od.call_id  call_id='521243ad953e-965inwuz1gku'; 

mysql reports:

error 1052 (23000): column 'call_id' in clause ambiguous 

when join 2 tables using column name identical in both tables, use using clause instead of on:

select timediff(to_seconds(od.created_at), to_seconds(oc.created_at))  on_connected oc  join on_disconnected od  using(call_id)                             -- eq. `od.call_id = oc.call_id` call_id='521243ad953e-965inwuz1gku'; -- no need specify table name here 

non save few key stokes, doing so, able reference column without specifying table name.