Help with a UNION!

[ShawnMolloy]

I am trying to determine what "Friends" a "User" has, based on a lookup table that holds a friends/user relationship information. The table looks like this:

UserId | FriendId
-----------------
1 | 3
7 | 9
4 | 3

1 (Shawn) is friends with 3 (Ashlee), so therefore Ashlee is also friends with shawn. I can't write a query like

SELECT * FROM FRIENDS WHERE FriendId=3

because that is only one half of the relationship (because 3 could also be the userid). This is my solution, but when I run it and search by the FriendID (Second part of the union) All I get is two instances of the UserId i searched for:

SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY A.UserId) 
		AS rowNum, A.*, B.UserName FROM WiseTopic_Friend A	
		INNER JOIN WiseTopic_User B  ON A.FriendId = B.UserId 
		WHERE a.userid=@UserId) 
	AS X
WHERE rowNum between 1 and 12

UNION

SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY A.UserId) 
		AS rowNum, A.*, B.UserName FROM WiseTopic_Friend A	
		INNER JOIN WiseTopic_User B  
		ON A.FriendId = B.UserId 
		WHERE a.friendid=@UserId) 
	AS X
WHERE rowNum between 1 and 12

To see this in action go here

http://wisetopic.com/profile/?UserId=1

and click on friends. You see all of Ashlees friends. If you click on Jenny's profile and click Friends you should see Ashlee, but instead you just see two instances of Jenny. I hope I am explaining this clearly!! Its pretty confusing.





Shawn Molloy
Seattle, WA

 

[rac2]

As they say in algebra friendship is not reflexive. Which I think means just because Shawn lists Ashlee as a friend does not mean that Ashlee lists Shawn.

But you wish to override that and show Shawn on Ashlee list even though there is no row for

UserId | FriendId
-----------------
3 | 1

In any case all friends identified by user Ashlee are

SELECT FriendId
FROM UserFriends
WHERE UserId = 3

And all users who identified Ashlee as a friend are

SELECT UserId
FROM UserFriends
WHERE FriendId = 3

And the union, sociometrically distorted as it may be, is

SELECT FriendId AS "MaybeFriend"
FROM UserFriends
WHERE UserId = 3

UNION

SELECT UserId
FROM UserFriends
WHERE FriendId = 3

 

[ShawnMolloy]

Hi Rac2

Thanks for your reply. I agree - the way you described is how it theoritically SHOULD work. If you look at my query above, and imagine the varible @USERID is equal to 3, then you will see that its exactly the same as what you just wrote out.

WHERE a.friendid=@UserId

The problem is I want to find out where any kind of relationship with UserId 1 exists. This is the result of the query that I wrote above.

UserId FriendId UserName
---------------------
1 3 Ashlee
11 1 Ashlee
1 4 harrisrj1
1 5 Jessica
1 7 Jenny
1 18 Jenni
1 37 adnauseam
1 52 shannon
1 8 naitp
1 9 celsog
1 10 spaceman
1 11 John
1 12 asdf


You can clearly see that this is incorrect by looking at the contents of the entire friends table:

UserId FriendId UserName
-----------------------------
1 3 Ashlee
1 4 harrisrj1
3 4 harrisrj1
1 5 Jessica
4 11 John
30 7 Jenny
1 7 Jenny
11 1 Ashlee
1 18 Jenni
34 4 harrisrj1
1 37 adnauseam
1 52 shannon
1 8 naitp
1 9 celsog
1 10 spaceman
1 11 John
1 12 asdf
1 13 May
1 14 Ali
1 15 asdfasdf
1 16 asdffes
1 17 fewfsaf4ef
1 18 Jenni
1 19 Kate
1 20 sdfij3
1 21 asdfsfes
1 22 asdfssfes
1 23 Kim
1 24 haiminis
1 25 billg
1 26 PhilGlass
1 27 Kristy
1 28 MrBigCityPlyr
ishw4r
1 29

Bascially I want to the ID of a USER (friend) where the USERID (1) exists in either the userid column, or the friends column. The cavet is.. I need the USERID if 1 is the friend's ID or the FRIENDID if the userid=1.


:S

Thanks,


Shawn Molloy
Seattle, WA

 

[PWise]

please post your complete Sql Statment with the @userid pramamteter

pwise

 

[ShawnMolloy]

Please refer to the first post - for your reference here it is:

SELECT * FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY A.UserId)
        AS rowNum, A.*, B.UserName FROM WiseTopic_Friend A    
        INNER JOIN WiseTopic_User B  ON A.FriendId = B.UserId
        WHERE a.userid=@UserId)
    AS X
WHERE rowNum between 1 and 12

UNION

SELECT * FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY A.UserId)
        AS rowNum, A.*, B.UserName FROM WiseTopic_Friend A    
        INNER JOIN WiseTopic_User B  
        ON A.FriendId = B.UserId
        WHERE a.friendid=@UserId)
    AS X
WHERE rowNum between 1 and 12

Thank you.

Shawn Molloy
Seattle, WA

 

[ESquared]

SELECT * FROM (
   SELECT
      UserID,
      FriendID
   UNION SELECT
      FriendID,
      UserID
   FROM Friends
) F
WHERE UserID = 3

Will list all of UserID 3's friends whether he has listed the friend himself or the friend has listed him. This query is inefficient because it hits the whole table. So let's do it another way:

SELECT DISTINCT --distinct needed in case both list the other as a friend
   UserID = 3,
   FriendID = CASE UserID WHEN 3 THEN FriendID ELSE UserID END
FROM Friends
WHERE
   UserID = 3 OR FriendID = 3

This should give better performance as there's no union and you're applying the where clause directly instead of grabbing the whole table and then selecting from that.

 

[ESquared]

Er, wait. Something's missing. Give me a minute...

 

[ESquared]

Actually, everything I wrote is fine except the missing "FROM Friends" in my first query.

rac2's code is golden, too.

Perhaps the problem is your rowcount limitation. If you are limiting to 12 rows, maybe you need to limit after the union instead of before?

Here's the code I used to create a test table:

create table Friends (UserID int, FriendID int)

declare @i int
declare @u int
declare @f int
set @i = 0
while @i < 100 begin
	set @u = rand()*30 + 1
	set @f = rand()*30 + 1
	if not exists (select * from friends where userid = @u and friendid = @f) begin
		insert friends select @u, @f
		set @i = @i + 1
	end
end

select * from friends order by userid, friendid
select * from friends where userid = 3 order by userid, friendid
select * from friends where friendid = 3 order by userid, friendid

 

[ESquared]

Er, I meant row_number not rowcount.

The best way to limit to 12 rows if you don't need the row_number in the final set is to use

SELECT TOP 12
...