help with query (select result from result)

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 (select result from result)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-11 : 11:49:35

Hi,

I have the following query that works, however I want to modify it slightly. I want to pass it 2 "userID"'s and effectively get the "friends in common". This query would be as if I ran the "select_friends" for 2 different userID's and then compared results. I think simple enough to understand but if you would like me to type up some dummy data plz let me know ! :)

Thanks very much for any help!
Mike123

New Query:




create PROCEDURE [dbo].[select_friends_inCommon] 
	(
	 @userID int,
	 @compareUserID int
	 )

AS SET NOCOUNT ON 

SELECT ..

Original Query, which is a simple "friendsList" query brings back a list.



create PROCEDURE [dbo].[select_friends] 
	(
	 @userID int
	)

AS SET NOCOUNT ON 

SELECT friendID, tblUserDetails.nameOnline
 
FROM tblFriends friend 

JOIN tblUserDetails on friend.friendID = tblUserDetails.userID 

WHERE friend.userID = @userID

Here is the table structure:




CREATE TABLE [dbo].[tblFriends](
	[UserID] [int] NOT NULL,
	[FriendID] [int] NOT NULL,
	[dateAdded] [smalldatetime] NULL
)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 12:03:40

give it a try and post your query
you can convert your sp into function so you can call it inline and do the comparison

--------------------
keeping it simple...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-11 : 12:16:05

Hey Jen,

I can't figure out how to start this =[ I'm not a full time SQL guy and get pretty rusty, not that I've done this stuff before .. anything to get me started is appreciated !! :)

thanks again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 12:23:03

I already have posted you the link, twice!
You have been working on this topic at least six months now.

Here is the link, again.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=72097

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-11 : 13:24:50

Hey Peso,

I respect your time and hate wasting anyones time who is helping me (especially yours since you have helped so frequently) and to the best of my knowledge (and sqlteam searching abilities :D) I have never read it before. I did receive some much appreciated help from you previously on on "friends of friends" and stuff very similar but not this "mutual friends" problem.

I'm reading this thread you linked me to, and I think my situation qualifies as what you have just posted since I only need MUTUAL friends. I think this is what I'm looking for, just trying to figure out how to adapt it. I don't think the recursive algorithm is necessary as you said its not for just mutual friends, but I would love to get into more complex stuff after I figure this stuff out. I am using sql2005 for the record as well.

Is it best to have a function and a SPROC for this approach ? I'm attempting to integrate what I've pasted below now, not quite following it exactly yet :)

You posted some more advanced stuff after, but as far as I can tell this is not what I'm looking for ?

Thanks again for the continued support

mike123

[code]
-- Prepare test data
CREATE TABLE Contacts (pFrom VARCHAR(2), pTo VARCHAR(2))

INSERT Contacts
SELECT 'A', 'B' UNION ALL
SELECT 'B', 'D' UNION ALL
SELECT 'C', 'A' UNION ALL
SELECT 'C', 'E' UNION ALL
SELECT 'G', 'C' UNION ALL
SELECT 'B', 'G' UNION ALL
SELECT 'F', 'D' UNION ALL
SELECT 'E', 'F'

-- This is the node chart of connections
/*
A - B
/ / \
C - G D
\ /
E - F
*/

SELECT mf.Name
FROM dbo.fnMutualFriends('C', 'B') mf
ORDER BY mf.Name

SELECT mf.Name
FROM dbo.fnMutualFriends('B', 'C') mf
ORDER BY mf.Name

-- Clean up
DROP TABLE Contacts
NB: The order of pFrom and pTo doesn't matter. Try for yourself to switch columns for some pFrom and pTo. You will get the same result.

CREATE FUNCTION dbo.fnMutualFriends
(
@Want1 VARCHAR(2),
@Want2 VARCHAR(2)
)
RETURNS @Mutual TABLE
(
Name VARCHAR(2)
)
AS

BEGIN
INSERT @Mutual
(
Name
)
SELECT x.Name
FROM (
SELECT pFrom Name
FROM Contacts
WHERE pTo = @Want1
UNION
SELECT pTo
FROM Contacts
WHERE pFrom = @Want1
) x
INNER JOIN (
SELECT pFrom Name
FROM Contacts
WHERE pTo = @Want2
UNION
SELECT pTo
FROM Contacts
WHERE pFrom = @Want2
) y ON y.Name = x.Name

RETURN
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:41:20

Edit the function to UserID instead of Name.

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-11 : 15:59:33

Hi Peso,

Thanks, I have done that and this is what I came up with. I am not getting the exact results I was expecting, but it is executing fine, so I'm going to check for orphaned data etc and make sure I joined the proper columns to each other. If anything sticks I'd love to know :)

Just wondering here, not sure if its efficient or should be done inside wondering, is it ok to execute the code like this? I am joining the function etc. I haven't really taken advantages of function before so just want to make sure I'm doing this efficiently

Thanks once again!
mike123

create PROCEDURE [dbo].[select_friends_inCommon]
(
@userID int,
@compareUserID int
)

AS SET NOCOUNT ON

SELECT mf.userID, UD.nameOnline
FROM dbo.fnMutualFriends(@userID, @compareUserID) mf

JOIN tblUserDetails UD on mf.userID = ud.userID

WHERE UD.active = 1
ORDER BY mf.userID

[code]
ALTER FUNCTION dbo.fnMutualFriends
(
@userID int,
@friendID int
)
RETURNS @Mutual TABLE
(
userID int
)
AS

BEGIN
INSERT @Mutual
(
userID
)
SELECT x.userID
FROM (
SELECT friendID userID
FROM tblFriends
WHERE userID = @userID
UNION
SELECT userID
FROM tblFriends
WHERE friendID = @userID
) x
INNER JOIN (
SELECT friendID userID
FROM tblFriends
WHERE userID = @friendID
UNION
SELECT userID
FROM tblFriends
WHERE friendID = @friendID
) y ON y.userID = x.userID

RETURN
END
[code]

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-12 : 04:25:00

I've implemented, and its executing fine but I am getting results different from what I am expecting. I think what I am trying to do is simpler than what the function is doing, but I am not sure.

What I want to do is, perhaps different than the defination of "mutual friends" I have provided data below to show the exact results I would like returned.

I want the result to bring back "what friends person #1 has, that person #2 has as well" . Not any degree of relations or anything.

With the following table structure, sproc, and data below, the following results should be brought back:

Results

userID / nameOnline

503 / billy
504 / bob

Thanks very much!
mike123





CREATE PROCEDURE [dbo].[select_friends_inCommon] 
	(
	 @userID int,
	 @compareUserID int
	 )

AS SET NOCOUNT ON 

SELECT		mf.userID, UD.nameOnline
FROM		dbo.fnMutualFriends(@userID, @compareUserID) mf

JOIN tblUserDetails UD on mf.userID = ud.userID

WHERE UD.active = 1



CREATE TABLE [dbo].[tblTest123](
	[UserID] [int] NOT NULL,
	[FriendID] [int] NOT NULL,
	[dateAdded] [smalldatetime] NULL
)



INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (500,501,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (500,502,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (500,503,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (500,504,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (500,505,getDate())

INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (1000,1001,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (1000,1002,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (1000,503,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (1000,504,getDate())
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (1000,1005,getDate())

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 04:40:52

I get that result!

exec select_friends_inCommon 500, 1000

returns

503
504

for me...

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-12 : 21:33:55

Hi Peso,

Your right, that data was brought back, but my pasted data wasnt complex enough to account for certain situations. It appears I didnt need the UNION for my specific query.

Just FYI, your function is now bringing back exactly what I need as seen below.

Thanks again, much appreciated !! :)

mike123



alter FUNCTION	[dbo].[fnMutualFriends]
(
	@userID int,
	@friendID int
)
RETURNS @Mutual TABLE
(
	userID int
)
AS

BEGIN
	INSERT		@Mutual
			(
				userID
			)
	SELECT		x.userID
	FROM		(
				SELECT		friendID userID
				FROM		tblFriends
				WHERE		userID = @userID and userID <> friendID
				
			) x
	INNER JOIN	(
				SELECT		friendID userID
				FROM		tblFriends
				WHERE		userID = @friendID and userID <> friendID
				
			) y ON y.userID = x.userID

	RETURN
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources