June 13, 2012 at 12:47 am
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]
June 14, 2012 at 1:36 pm
You might get more visibility and responses if you posted this somewhere other than the Certification forum...
June 14, 2012 at 2:29 pm
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
June 15, 2012 at 11:43 pm
Thank you for your reply 🙂
August 31, 2014 at 11:00 pm
UserProfile is a table which contains all the details of particular user.
Friends 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)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply