List:General Discussion« Previous MessageNext Message »
From:Nhadie Date:June 2 2006 1:50pm
Subject:help on SQL JOIN
View as plain text  
Hi All,

I have this test table

+----------+------+------+------+--------+----------+
| username | key1 | key2 | key3 | method | timer    |
+----------+------+------+------+--------+----------+
| nhadie   | aaa  | bbb  | ccc  | INVITE | 00:00:10 |
| nhadie   | aaa  | bbb  | ccc  | ACK    | 00:00:14 |
| nhadie   | aaa  | bbb  | ccc  | INVITE | 00:00:16 |
| nhadie   | aaa  | bbb  | ccc  | ACK    | 00:00:20 |
| nhadie   | aaa  | bbb  | ccc  | INVITE | 00:00:22 |
| nhadie   | aaa  | bbb  | ccc  | ACK    | 00:00:26 |
| nhadie   | aaa  | bbb  | ccc  | BYE    | 00:00:42 |
| nhadie   | ddd  | fff  | eee  | INVITE | 00:00:20 |
| nhadie   | ddd  | fff  | eee  | ACK    | 00:00:24 |
| nhadie   | ddd  | fff  | eee  | INVITE | 00:00:26 |
| nhadie   | ddd  | fff  | eee  | ACK    | 00:00:30 |
| nhadie   | ddd  | fff  | eee  | INVITE | 00:00:32 |
| nhadie   | ddd  | fff  | eee  | ACK    | 00:00:36 |
| nhadie   | ddd  | fff  | eee  | BYE    | 00:00:52 |
+----------+------+------+------+--------+----------+

I tried this query:

select t1.username as username, t1.method as t1method, t2.method as
t2method, t1.timer as start,
t2.timer as stop, unix_timestamp(t2.timer)-unix_timestamp(t1.timer) as
timediff
from test t1,test t2 where (t1.method = 'INVITE' and t2.method = 'BYE') and
((t1.key1 = t2.key1)
and (t1.key2 = t2.key2) and (t1.key3 = t2.key3));

and this is the result:

+----------+----------+----------+----------+----------+----------+
| username | t1method | t2method | start    | stop     | timediff |
+----------+----------+----------+----------+----------+----------+
| nhadie   | INVITE   | BYE      | 00:00:10 | 00:00:42 | 00:00:32 |
| nhadie   | INVITE   | BYE      | 00:00:16 | 00:00:42 | 00:00:26 |
| nhadie   | INVITE   | BYE      | 00:00:22 | 00:00:42 | 00:00:20 |
| nhadie   | INVITE   | BYE      | 00:00:20 | 00:00:52 | 00:00:32 |
| nhadie   | INVITE   | BYE      | 00:00:26 | 00:00:52 | 00:00:26 |
| nhadie   | INVITE   | BYE      | 00:00:32 | 00:00:52 | 00:00:20 |
+----------+----------+----------+----------+----------+----------+

but what i want to actually achieve is to get only the time of the first
INVITE and then it's corresponding BYE
so it should look like this

+----------+----------+----------+----------+----------+----------+
| username | t1method | t2method | start    | stop     | timediff |
+----------+----------+----------+----------+----------+----------+
| nhadie   | INVITE   | BYE      | 00:00:10 | 00:00:42 | 00:00:32 |
| nhadie   | INVITE   | BYE      | 00:00:20 | 00:00:52 | 00:00:32 |
-------------------------------------------------------------------

Is that possible? What else can I do to have this output?
Thank You in Advanced.

Regards,
Nhadie

________________________________________________
Message sent using UebiMiau 2.7

Thread
help on SQL JOINNhadie2 Jun
Re: help on SQL JOINPeter Brawley2 Jun