help with query - relating data

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)

help with query - relating data

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-27 : 10:03:41

Hi,

I have a table that I am trying to come up with a query for. I want to bring back a query that is passed a userID and returns data that matches the following description.

"People who looked at your userID, also looked at the following userID's" We will determine these people by the IP column.

I really have no idea how to get started on this, or how complex and resource intense its going to be.

Any help is much appreciated!

Thanks once again :)

mike123

Table Structure:

tblViewsLog

userID,IP,city,
58 71.204.xx.xx MARIETTA 2007-01-18 14:12:00
15 212.29.xx.xxx - 2007-01-18 14:14:00
41 63.148.x.xxx BROOKLYN 2007-01-18 14:17:00
22 64.46.x.xxx VANCOUVER 2007-01-18 14:21:00
2 208.181.xx.xxx VANCOUVER 2007-01-18 15:00:00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 10:08:29

quote:
"People who looked at your userID, also looked at the following userID's" We will determine these people by the IP column.

Can you explain more on the statement ? Maybe with some sample data and the expected result

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-27 : 11:21:59

Lets say for instance we want to run the SPROC on userID 1

We will find out that userID 1 has had "127.0.0.1" and "192.168.1.1" and "255.255.0.1" look at it.

Going to the next level we want to find out what other userID's the IP's belonging to userID1 ("127.0.0.1" and "192.168.1.1" and "255.255.0.1") look at.

We see that they look at userID 2x ("2 127.0.0.1 myHouse 2007-01-18 14:14:00, 2 255.255.0.1 myHouse 2007-01-18 15:00:00)

This should be the #1 result (userID:2)

tblRelatedViewers

mapID / totalViewers

2 / 2
3 / 1
4 / 1

Does this make more sense now? It's pretty confusing to explain I think so please let me know if I can elaborate any further.

Thanks again!
mike123

userID,IP,city,viewDate

1 127.0.0.1 myHouse 2007-01-18 14:12:00
2 127.0.0.1 myHouse 2007-01-18 14:14:00
3 127.0.0.1 myHouse 2007-01-18 14:17:00

1 192.168.1.1 myHouse 2007-01-18 14:21:00
4 192.168.1.1 myHouse 2007-01-18 15:00:00

1 255.255.0.1 myHouse 2007-01-18 15:00:00
2 255.255.0.1 myHouse 2007-01-18 15:00:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 12:29:22

Take a look at this function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
It can get parts of a string, borh from left and from right.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-02-27 : 13:04:00

Hey Peter,

Thanks once again for the help. I have to admit I think this is one of the more complicated queries I have attempted and I don't really follow your text parser or why I need to work with strings. I'm pretty lost, if you think I have any hope on this one - any help is much appreciated!

Thanks again :)
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 14:01:58

Oh, i thought your data was space delimited. My mistake.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 14:16:17

[code]-- Prepare sample data
declare @t table (userID int, IP varchar(15), city varchar(20), viewDate datetime)

insert @t
select 1, '127.0.0.1', 'myHouse', '2007-01-18 14:12:00' union all
select 2, '127.0.0.1', 'myHouse', '2007-01-18 14:14:00' union all
select 3, '127.0.0.1', 'myHouse', '2007-01-18 14:17:00' union all
select 1, '192.168.1.1', 'myHouse', '2007-01-18 14:21:00' union all
select 4, '192.168.1.1', 'myHouse', '2007-01-18 15:00:00' union all
select 1, '255.255.0.1', 'myHouse', '2007-01-18 15:00:00' union all
select 2, '255.255.0.1', 'myHouse', '2007-01-18 15:00:00'

-- Is this what you want?
SELECT t.UserID AS MapID,
COUNT(*) AS TotalViews
FROM @t AS t
INNER JOIN (
SELECT DISTINCT IP
FROM @t
WHERE UserID = 1
) AS x ON x.IP = t.IP
WHERE t.UserID <> 1
GROUP BY t.UserID
ORDER BY t.UserID[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources