Query Help - 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 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

Steveyj
Starting Member

6 Posts

Posted - 2007-01-30 : 07:08:38

OK i have 3 tables i need to work with

a players table, a last viewed table and a block table

what i need to do is check who last viewed a profile where the either player hasn't been blocked.

e.g:
playerid 1 has been viewed by playerid 2 and 3 with player 2 being the last person to view player 1

player 1 has blocked player 2 so player 3 should display as the last person who viewed player 1.

PLayers table brings back the players name of who viewed, last viewed stores playerid, lookedatplayerid and lastvieweddate, block table stores playerid, lookedatplayerid and block (bit)

if you need more info please let me know

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 07:44:25

can you post the table structure, some sample data and the result that you want ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 08:26:47

I hate these dating sites algorithms

SELECT		lv.PlayerID,
		d.LookedAtPlayerID,
		lv.LstViewDate
FROM		(
			SELECT		lv.LookedAtPlayerID,
					MAX(lv.LastViewDate) AS MaxLastViewDate
			FROM		LastViewed AS lv
			LEFT JOIN	Blocked AS b ON b.PlayerID = lv.PlayerID AND b.LookedAtPlayerID = lv.LookedAtPlayerID AND b.Block = 1
			WHERE		b.PlayerID IS NULL
			GROUP BY	lv.LookedAtPlayerID
		) AS d
INNER JOIN	LastViewed AS lv ON lv.LookedAtPlayerID = d.LookedAtPlayerID AND lv.LastViewDate = d.MaxLastViewDate

Peter Larsson
Helsingborg, Sweden

Steveyj
Starting Member

6 Posts

Posted - 2007-01-30 : 08:31:06

players table

playerid screen name
1 bob
2 JO
3 Lisa

lastviewed tbl
playerid Lookedatplayerid dateviewed
2 1 30/1/2007
3 1 26/1/2007

block tbl
playerid lookedatplayer block
1 2 1

at the mo the last viewed is player 2 to player but player 2 is blocked by player 1 so it should show player 3 instead

Steveyj
Starting Member

6 Posts

Posted - 2007-01-30 : 09:54:25

Peter i tried what u said with no luck

can anyone else help out it just need to check if a player is blocked or not and if they are display the next person whom has viewed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 10:06:40

So in the Block table, Player and LookedAtPlayer column names has new meanings?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 10:08:12

[code]-- prepare sample data
set dateformat dmy

declare @players table (playerid int, screenname varchar(20))

insert @players
select 1, 'bob' union all
select 2, 'JO' union all
select 3, 'Lisa'

declare @lastviewed table (playerid int, Lookedatplayerid int, dateviewed datetime)

insert @lastviewed
select 2, 1, '30/1/2007' union all
select 3, 1, '26/1/2007'

declare @block table (playerid int, lookedatplayer int, block bit)

insert @block
select 1, 2, 1

-- show the data
SELECT lv.LookedAtPlayerID,
d.PlayerID,
lv.DateViewed
FROM (
SELECT lv.PlayerID,
MAX(lv.DateViewed) AS MaxLastViewDate
FROM @LastViewed AS lv
LEFT JOIN @Block AS b ON b.PlayerID = lv.LookedAtPlayerID AND b.LookedAtPlayer = lv.PlayerID AND b.Block = 1
WHERE b.PlayerID IS NULL
GROUP BY lv.PlayerID
) AS d
INNER JOIN @LastViewed AS lv ON lv.PlayerID = d.PlayerID AND lv.DateViewed = d.MaxLastViewDate[/code]

Peter Larsson
Helsingborg, Sweden

Steveyj
Starting Member

6 Posts

Posted - 2007-01-30 : 10:10:54

OK the playerid is the person who done the action and the lookedatplayer is the person the person who the action is going against?

i.e plkayer 2 looks at player 1 so the playerid would be 2 and the lookedatplayerid would be 1 and so forth.

My Sql knowledge is ok but i've never done a from with a selected innerjoin before

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 10:13:25

You can't use INNER JOIN to search for combinations NOT THERE.
Copy and run my last suggestion...

Peter Larsson
Helsingborg, Sweden

Steveyj
Starting Member

6 Posts

Posted - 2007-01-30 : 10:27:46

block table isn't a temp table it's an actual table that has live data of everyone blocked by any dynamic playerid does this matter?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 10:30:04

As commented in the code, the first statements are only there to prepare the sample data provided by you.

The "real" code is the query after "show the data" comment.
In this query, replace my column names with the ones YOU use in YOUR environment...

Peter Larsson
Helsingborg, Sweden

Steveyj
Starting Member

6 Posts

Posted - 2007-01-30 : 11:28:40

Query worked a treat... only a couple of issues with it.. doesn't bring back the screenname and it runs to slow per specified playerid but thank you so much it did work a treat otherwise

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 12:06:40

1) Add a proper INDEX and the query will run very fast.
2) INNER JOIN the Player table twice to get the PlayerNames.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources