 | | | help on SQL JOIN | help on SQL JOIN 2006-06-02 - By Peter Brawley
Back
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|