showing Mutual friends through query

  • 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]

  • You might get more visibility and responses if you posted this somewhere other than the Certification forum...

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you for your reply 🙂

  • 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