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-06 : 17:38:19

Hi,

I have a table called "tblUserVote" as seen below. I am trying to create a query where I pass a "userID" only to the query and get the following results.

I would like to bring back rows that contain results where a user has voted for a person, and that person has also voted them back. (this means there is a voteForID and a voterID of both the userID's involved)

I have this query which is similar, but it specifies 1 voteForID and one voterID so only brings back one row. I want to open it up and bring back all situations where a person has voted for another, and vice versa.

SAMPLE DATA

voteForID / voterID / date / points

500 1000 2004-02-24 01:32:00 100
1000 500 2004-02-24 01:33:00 50
106380 142083 2004-02-24 01:33:00 100
136904 122048 2004-02-24 01:34:00 95
48245 142083 2004-02-24 01:34:00 100
122048 136904 2004-02-24 01:34:00 95

DESIRED RESULTS

voteForID,voteFor_points,voteFor_date,voterID,voter_points,voter_date
500, 100, 2004-02-24 01:32:00, 1000, 50, 2004-02-24 01:33:00

This is the query I am working off of. I'm not sure if this can be modified or if it needs a complete rewrite.


SELECT 
A.voteForID AS voteForID_1, 
A.voterID AS VoterID_1,
A.date AS Date_1,
A.points AS points_given,
B.voteForID AS voteForID_2, 
B.voterID AS VoterID_2,
B.date AS Date_2,
B.points AS points_received 

FROM tblUserVote A, tblUserVote B 

WHERE (A.voteForID = @voteForID and A.voterID = @userID) and (B.voteForID = @userID and B.voterID = @voteForID)

Here is the table structure


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
)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-06 : 17:58:58

How about this?

DECLARE @voteforid int

SET @voteforid = 500

DECLARE @tblUserVote TABLE(
[voteID] [int] IDENTITY(1,1) NOT NULL,
[voteForID] [int] NOT NULL,
[voterID] [int] NOT NULL,
[date] [smalldatetime] NOT NULL,
[points] [tinyint] NOT NULL
)

INSERT INTO @tblUserVote
SELECT 500, 1000, '2004-02-24 01:32:00', 100
union select
1000 ,500, '2004-02-24 01:33:00', 50
union select
106380 ,142083, '2004-02-24 01:33:00' ,100
union select
136904, 122048, '2004-02-24 01:34:00' ,95
union select
48245 ,142083, '2004-02-24 01:34:00' ,100
union select
122048 ,136904 ,'2004-02-24 01:34:00' ,95

select * from @tbluservote
select a.voteforid,a.points,a.date,b.voteforid,b.points,b.date
from
@tblUserVote a
inner join
@tblUserVote b
ON
a.voteforid = b.voterid
and a.voterid = b.voteforid

WHERE
a.voteforid = @voteforid

Jim

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-06 : 19:02:09

thats exactly it!

thanks so much :)

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 08:08:15

This is still the exact same answer I gave you in August 31, 2007.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88718

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