help with query - 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)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-23 : 03:37:52

Hi,

I have the following query and table, they are functioning properly however I would like to extend on to the functionality. This query brings back the latest votes, however I would like to bring back the latest votes where users have reciprocated votes with the user they have voted for. Not sure exactly how to do this so any help is appreciated. I have included the table structure below as well as the query so far.

Thanks very much!

mike123




select top 10 

voteForID,voterID,UV.points,UV.date,UD1.nameOnline as voteFor_nameOnline, UD2.nameOnline as voter_nameOnline

FROM tbluservote UV

JOIN tblUserDetails UD1 on UD1.userID = voteForID
JOIN tblUserDetails UD2 on UD2.userID = voterID

order by UV.date desc







CREATE TABLE [dbo].[tblUserVote](
	[voteID] [int] IDENTITY(1,1) NOT NULL,
	[voteForID] [int] NOT NULL,
	[voterID] [int] NOT NULL,
	[date] [smalldatetime] NOT NULL,
	[points] [tinyint] NOT NULL
(

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 03:45:18

Add

JOIN tbluservote AS UV2
ON UV2.voteForID = UV1.voterID
AND UV2.voterID= UV1.voteForID

??

Kristen

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-23 : 04:31:09

thats what I was lookin for thx so much !! :)

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 04:55:07

You will need to prefix the existing columns that use tbluservote with UV of course.


select top 10 

UV.voteForID,UV.voterID,UV.points,UV.date,UD1.nameOnline as voteFor_nameOnline, UD2.nameOnline as voter_nameOnline

FROM tbluservote UV

JOIN tblUserDetails UD1 on UD1.userID = UV.voteForID
JOIN tblUserDetails UD2 on UD2.userID = UV.voterID

JOIN tbluservote AS UV2
ON UV2.voteForID = UV1.voterID
AND UV2.voterID= UV1.voteForID

order by UV.date desc

if I've found all of them!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 03:35:27

I would like to see this system when it's done.
It seems to be built with major help from SQLTeam

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources