Join two tables. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join two tables.

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-22 : 13:38:30

Table 1

user_id time_stamp site_id ad_id
185393480 3/14/2010 20:40:06 201073 223107920

Table 2
user_id time_stamp site_id ad_id
185393480 3/14/2010 20:40:02 201073 223107920
185393480 3/16/2010 11:40:03 201073 223107920
185393480 3/20/2010 10:36:00 201073 223107920
185393480 3/22/2010 7:26:48 201073 223107920
185393480 3/22/2010 22:52:14 201073 223107920
185393480 3/29/2010 23:08:38 201073 223107920
185393480 3/31/2010 8:44:07 201073 223107920
185393480 3/31/2010 18:29:03 201073 223107920
185393480 3/13/2010 13:18:24 201073 222942986
185393480 3/15/2010 22:57:00 201073 223107920

Result

user_id time_stamp site_id ad_id click
185393480 3/14/2010 20:40:02 201073 223107920 1
185393480 3/16/2010 11:40:03 201073 223107920 0
185393480 3/20/2010 10:36:00 201073 223107920 0
185393480 3/22/2010 7:26:48 201073 223107920 0
185393480 3/22/2010 22:52:14 201073 223107920 0
185393480 3/29/2010 23:08:38 201073 223107920 0
185393480 3/31/2010 8:44:07 201073 223107920 0
185393480 3/31/2010 18:29:03 201073 223107920 0
185393480 3/13/2010 13:18:24 201073 222942986 0
185393480 3/15/2010 22:57:00 201073 223107920 0

Result:
Add Click in column2 with value 1 and 0 if same user_id, ad_id, site_id and timestamp differ is less than 120 seconds.

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-22 : 13:51:05

I am using this but this does not work.

select a.*, case when b.user_id is not null then 1 else 0 end as click
from table1 a LEFT OUTER JOIN table2 b
on a.SITE_ID=b.site_id and a.AD_ID=b.ad_id
and a.USER_ID=b.user_id
and datediff(ss,a.time_stamp,b.time_stamp)<=120

It is giving me this output:

user_id time_stamp site_id ad_id click
185393480 3/14/2010 20:40:02 201073 223107920 1
185393480 3/16/2010 11:40:03 201073 223107920 1
185393480 3/20/2010 10:36:00 201073 223107920 1
185393480 3/22/2010 7:26:48 201073 223107920 1
185393480 3/22/2010 22:52:14 201073 223107920 1
185393480 3/29/2010 23:08:38 201073 223107920 1
185393480 3/31/2010 8:44:07 201073 223107920 1
185393480 3/31/2010 18:29:03 201073 223107920 1
185393480 3/13/2010 13:18:24 201073 222942986 0
185393480 3/15/2010 22:57:00 201073 223107920 1

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-22 : 13:51:35

The time stamp function is not working properly? Why?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-22 : 14:21:26

You have to use ABS to get the absolute values

quote:
Originally posted by tamancha.1

I am using this but this does not work.

select a.*, case when b.user_id is not null then 1 else 0 end as click
from table1 a LEFT OUTER JOIN table2 b
on a.SITE_ID=b.site_id and a.AD_ID=b.ad_id
and a.USER_ID=b.user_id
and datediff(ss,a.time_stamp,b.time_stamp)<=120

It is giving me this output:

user_id time_stamp site_id ad_id click
185393480 3/14/2010 20:40:02 201073 223107920 1
185393480 3/16/2010 11:40:03 201073 223107920 1
185393480 3/20/2010 10:36:00 201073 223107920 1
185393480 3/22/2010 7:26:48 201073 223107920 1
185393480 3/22/2010 22:52:14 201073 223107920 1
185393480 3/29/2010 23:08:38 201073 223107920 1
185393480 3/31/2010 8:44:07 201073 223107920 1
185393480 3/31/2010 18:29:03 201073 223107920 1
185393480 3/13/2010 13:18:24 201073 222942986 0
185393480 3/15/2010 22:57:00 201073 223107920 1

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-22 : 14:26:18

select a.*, case when abs(datediff(ss,a.time_stamp, b.time_stamp))<=120 then 1 else 0 end as click
from tab2 a join tab1 b
on a.site_id = b.site_id and a.ad_id = b.ad_id and a.user_id = b.user_id

quote:
Originally posted by hanbingl

You have to use ABS to get the absolute values

quote:
Originally posted by tamancha.1

I am using this but this does not work.

select a.*, case when b.user_id is not null then 1 else 0 end as click
from table1 a LEFT OUTER JOIN table2 b
on a.SITE_ID=b.site_id and a.AD_ID=b.ad_id
and a.USER_ID=b.user_id
and abs(datediff(ss,a.time_stamp,b.time_stamp))<=120

It is giving me this output:

user_id time_stamp site_id ad_id click
185393480 3/14/2010 20:40:02 201073 223107920 1
185393480 3/16/2010 11:40:03 201073 223107920 1
185393480 3/20/2010 10:36:00 201073 223107920 1
185393480 3/22/2010 7:26:48 201073 223107920 1
185393480 3/22/2010 22:52:14 201073 223107920 1
185393480 3/29/2010 23:08:38 201073 223107920 1
185393480 3/31/2010 8:44:07 201073 223107920 1
185393480 3/31/2010 18:29:03 201073 223107920 1
185393480 3/13/2010 13:18:24 201073 222942986 0
185393480 3/15/2010 22:57:00 201073 223107920 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources