Get a result by comparing two tables with an identical column

Question

mysql> select * from on_connected;
+----+-----------+-------------+---------------------------+---------------------+
| id | extension | destination | call_id                   | created_at          |
+----+-----------+-------------+---------------------------+---------------------+
| 11 |   1111111 | 01155555551 | 521243ad953e-965inwuz1gku | 2013-08-19 17:11:53 |
+----+-----------+-------------+---------------------------+---------------------+

mysql> select * from 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 is a time difference of 4sec between the two. I would like to calculate the difference using a query of some type. I'm aware of TIMEFIFF() and joins but lack the skills to form the query at this point.

Here's my attempt thus far:

SELECT TIMEDIFF(to_seconds(od.created_at), to_seconds(oc.created_at)) 
FROM on_connected oc 
JOIN on_disconnected od 
ON oc.call_id=od.call_id 
WHERE call_id='521243ad953e-965inwuz1gku';

Mysql reports:

ERROR 1052 (23000): Column 'call_id' in where clause is ambiguous

Answer 1

In your where clause change

WHERE call_id='521243ad953e-965inwuz1gku';

to

WHERE oc.call_id='521243ad953e-965inwuz1gku';

or

WHERE od.call_id='521243ad953e-965inwuz1gku';

doesn't matter.

Answer 2

If you want the differences for all times:

SELECT TIME_TO_SEC(TIMEDIFF(od.created_at, oc.created_at)) 
FROM on_connected oc
JOIN on_disconnected od ON od.call_id = oc.call_id

Demo

For a single call_id, you need to alias the column name in the filter:

WHERE oc.call_id = '521243ad953e-965inwuz1gku'

Demo

Answer 3

try oc.call_id in the where clause.

although the values will have matched at this point, the sql parser still needs to know which one you're referring to.

Answer 4

When you JOIN two tables using a column whose name is identical in both tables, you could use the USING clause instead of ON:

SELECT TIMEDIFF(to_seconds(od.created_at), to_seconds(oc.created_at)) 
FROM on_connected oc 
JOIN on_disconnected od 
USING(call_id)                             -- eq. to `od.call_id = oc.call_id`
WHERE call_id='521243ad953e-965inwuz1gku'; -- no need to specify the table name here

Non only this will save a few key stokes, but by doing so, you will be able to reference that column without specifying the table name.