CODEWITHSUNDEEP
mysql
cookie
cookie

Reputation: 2728

Get a result by comparing two tables with an identical column

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

Upvotes: 2

Views: 111

Answers (4)

Sylvain Leroux
Sylvain Leroux

Reputation: 51980

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.

Upvotes: 0

user2366842
user2366842

Reputation: 1231

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.

Upvotes: 1

Kermit
Kermit

Reputation: 34054

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

Upvotes: 1

fancyPants
fancyPants

Reputation: 51868

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.

Upvotes: 1

Related Questions