Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

showing Mutual friends through query Expand / Collapse
Author
Message
Posted Wednesday, June 13, 2012 12:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:47 AM
Points: 15, Visits: 73
Hi

I have two tables one is for users and other is for user friends. now what i want i want to show the mutual friends on every friend request. In friends table i have used two fields user id and friend id. user is sending request to add friend i have status that remains false until friend accept his request. i want to show mutual friends on every friend request following is the structure of my tables.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_user_friends]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_user_friends](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[userid] [bigint] NULL,
[frndid] [bigint] NULL,
[friendrequestedOn] [datetime] NULL CONSTRAINT [DF_tbl_user_friends_friendrequestedOn] DEFAULT (getdate()),
[status] [bit] NULL,
CONSTRAINT [PK_tbl_user_friends] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_usermain]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_usermain](
[user_id] [bigint] IDENTITY(1,1) NOT NULL,
[user_type_id] [bigint] NULL,
[user_fname] [nvarchar](50) NULL,
[user_password] [nvarchar](50) NULL,
[user_lname] [nvarchar](50) NULL,
[user_username] [nvarchar](50) NULL,
[user_bday] [nvarchar](500) NULL,
[user_gender] [nvarchar](50) NULL,
[user_contact] [bigint] NULL,
[user_address] [nvarchar](500) NULL,
[user_address2] [nvarchar](500) NULL,
[user_country] [nvarchar](50) NULL,
[user_city] [nvarchar](50) NULL,
[user_state] [nvarchar](50) NULL,
[user_postalcode] [bigint] NULL,
[user_fax] [bigint] NULL,
[user_email] [nvarchar](50) NULL,
[user_alter_email] [nvarchar](50) NULL,
[user_img] [nvarchar](500) NULL,
[user_status] [bit] NULL,
[user_joiningdate] [datetime] NULL CONSTRAINT [DF_tbl_usermain_user_joiningdate] DEFAULT (getdate()),
[user_random] [nvarchar](50) NULL,
[user_activate] [nvarchar](50) NULL CONSTRAINT [DF_tbl_usermain_user_activate] DEFAULT (N'inactive'),
[user_desc] [nvarchar](1000) NULL,
[user_signature] [nvarchar](500) NULL,
[user_vedio] [nvarchar](500) NULL,
[user_vedio_desc] [nvarchar](500) NULL,
[user_notification] [nvarchar](50) NULL,
[user_newsletter] [nvarchar](50) NULL,
[user_rating] [bigint] NULL CONSTRAINT [DF_tbl_usermain_user_rating] DEFAULT ((0)),
[tot_votes] [bigint] NULL CONSTRAINT [DF_tbl_usermain_tot_votes] DEFAULT ((0)),
[profile_yourself] [nvarchar](500) NULL,
[profile_species] [nvarchar](500) NULL,
[profile_memorable_fish] [nvarchar](500) NULL,
[profile_dream_fish_trip] [nvarchar](500) NULL,
[profile_excuse] [nvarchar](500) NULL,
CONSTRAINT [PK_tbl_usermain] PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tbl_usermain_tbl_type]') AND parent_object_id = OBJECT_ID(N'[dbo].[tbl_usermain]'))
ALTER TABLE [dbo].[tbl_usermain] WITH CHECK ADD CONSTRAINT [FK_tbl_usermain_tbl_type] FOREIGN KEY([user_type_id])
REFERENCES [dbo].[tbl_type] ([type_id])
GO
ALTER TABLE [dbo].[tbl_usermain] CHECK CONSTRAINT [FK_tbl_usermain_tbl_type]
Post #1314879
Posted Thursday, June 14, 2012 1:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:27 PM
Points: 109, Visits: 957
You might get more visibility and responses if you posted this somewhere other than the Certification forum...
Post #1316168
Posted Thursday, June 14, 2012 2:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,903, Visits: 32,143
i think either of these will do what you are asking:
SELECT * 
FROM tbl_usermain FirstGuy
INNER JOIN tbl_user_friends FirstFriends
ON FirstGuy.user_id = FirstFriends.user_id
LEFT OUTER JOIN tbl_usermain SecondGuy
on FirstFriends.user_id = SecondGuy.user_id
INNER JOIN tbl_user_friends SecondFriends
on SecondGuy.user_id = SecondFriends.user_id
WHERE FirstFriends.user_id = SecondFriends.user_id
AND FirstGuy.user_id = 42 --Bob
AND SecondFriends..user_id = 38 --Jeff

SELECT *
FROM tbl_usermain FirstGuy
INNER JOIN tbl_user_friends FirstFriends
ON FirstGuy.user_id = FirstFriends.user_id
INNER JOIN tbl_user_friends SecondFriends
on FirstFriends.user_id = SecondFriends.user_id
WHERE FirstFriends.user_id = SecondFriends.user_id
AND FirstGuy.user_id = 42 --Bob
AND SecondFriends..user_id = 38 --Jeff



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1316200
Posted Friday, June 15, 2012 11:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:47 AM
Points: 15, Visits: 73
Thank you for your reply
Post #1316951
Posted Sunday, August 31, 2014 11:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 31, 2014 10:53 PM
Points: 1, Visits: 1
[color=#de0404]UserProfile[/color] is a table which contains all the details of particular user.
[color=#de0404]Friends[/color] is a table which conatins who have sent a friend request and accepted friends.
In friends table status =1 means it is friend request is pending and if status = 2 means friends request is accepted.

I am writing two queries which returns the friends of both users (current user and another user)
Using INTERSECT keyword it will return who are the mutual friends between 2 users.

select up.UserId, up.UserName,up.PhotoPath from UserProfile up where up.UserId in
(select FriendID from Friends f where f.Status=2 and f.UserID=29
union select f1.UserID from Friends f1 where f1.Status=2 and f1.FriendID=29)
INTERSECT
select up.UserId, up.UserName,up.PhotoPath from UserProfile up where up.UserId in
(select FriendID from Friends f where f.Status=2 and f.UserID=28
union select f1.UserID from Friends f1 where f1.Status=2 and f1.FriendID=28)
Post #1609236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse