From: Peter Brawley
Date: June 2 2006 3:37pm
Subject: Re: help on SQL JOIN
List-Archive: http://lists.mysql.com/mysql/198543
Message-Id: <44805B4B.9010201@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Nhadie,

> ...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 |
>-------------------------------------------------------------------

It can be done with subqueries, or with a left join on a timer 
comparison, which will likely be quicker:

SELECT
  t1.username as username,
  t1.method as t1meth, t2.method as t2meth, t3.method as t3meth,
  t1.timer as start, t2.timer as stop,
  TIMEDIFF(t2.timer,t1.timer) as timediff
FROM test AS t1
INNER JOIN test AS t2
  ON t1.key1 = t2.key1 AND t1.key2 = t2.key2 AND t1.key3 = t2.key3
LEFT JOIN test AS t3
  ON t1.key1 = t3.key1 AND t1.key2 = t3.key2 AND t1.key3 = t3.key3 AND 
t3.method='INVITE'
  AND t1.timer > t3.timer
WHERE t1.method='INVITE'
  AND t2.method='BYE'
  AND t3.key1 IS NULL;
+----------+--------+--------+--------+----------+----------+----------+
| username | t1meth | t2meth | t3meth | start    | stop     | timediff |
+----------+--------+--------+--------+----------+----------+----------+
| nhadie   | INVITE | BYE    | NULL   | 00:00:10 | 00:00:42 | 00:00:32 |
| nhadie   | INVITE | BYE    | NULL   | 00:00:20 | 00:00:52 | 00:00:32 |
+----------+--------+--------+--------+----------+----------+----------+

There's a brief discussion of this query pattern at 
http://www.artfulsoftware.com/queries.php#5.

PB

-----

Nhadie wrote:
> 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
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006