How to Get Data from master table

Question

0.00/5 (No votes)

See more:

Hello to all,

I have two table in my database 1)Usermaster 2) Friend Requestmaster

Inside Usermaster i have filed like UserId UserName for Exmple

UserId UserName
1             A
2             b
3             c
4             d

In Friend Request Master i have filed like ReqId SenderId RecieverId for Example

RepId SenderId Recieverid
1             1             2
2             3             1

If i input B than i get C,D is not a friend of B and if i check C than i get B,D is not friend of C.
and if i input D than i get A,B,C is not friend of D..

how i can do this please help...

Posted 24-Jul-14 1:08am

Ankit Patel(AP)

Updated 24-Jul-14 1:12am

v2

Add a Solution

Comments

Prakriti Goyal 24-Jul-14 7:44am

you got your ans?

Ankit Patel(AP) 26-Jul-14 5:05am

This error display when use your query

The correlation name 'UM' is specified multiple times in a FROM clause.

Ankit Patel(AP) 28-Jul-14 7:54am

No..

2 solutions

Solution 1

At the first look, it should be something similar to:
SQL

SELECT RM.RepId, UM.UserName AS Sender, UM.UserName AS Receiver FROM Requestmaster AS RM LEFT JOIN Usermaster AS UM ON RM.SenderId = UM.UserId LEFT JOIN Usermaster AS UM1 ON RM.ReceiverId = UM.UserId

[EDIT]
Here is working sample:

SQL

DECLARE @UserMaster TABLE (UserId INT IDENTITY(1,1), UserName VARCHAR(30))

INSERT INTO @UserMaster (UserName)
VALUES('user a'),('user b'),('user c'),('user d')
 
DECLARE @RequestMaster TABLE (RepId INT IDENTITY(1,1), SenderId INT, Recieverid INT)
 
INSERT INTO @RequestMaster ( SenderId, Recieverid)
VALUES(1, 2), (3,1)


SELECT RM.RepId, UM.UserName AS Sender, (SELECT UserName FROM @UserMaster WHERE UserId  = RM.Recieverid ) AS Reciever
FROM @RequestMaster  AS RM
    LEFT JOIN @UserMaster  AS UM ON RM.SenderId = UM.UserId

Result:

1   user a  user b
2   user c  user a

[/EDIT]

Posted 24-Jul-14 1:19am

Maciej Los

Updated 28-Jul-14 0:44am

v2

Comments

Ankit Patel(AP) 26-Jul-14 5:07am

i want Unfriend data which are not friend of one user

Ankit Patel(AP) 26-Jul-14 5:08am

This error display when use your query

The correlation name 'UM' is specified multiple times in a FROM clause.

Maciej Los 28-Jul-14 1:48am

Change second alias.

Ankit Patel(AP) 28-Jul-14 6:29am

NO Data Display...Write Query..

SELECT RM.Request_Id, UM.FirstName AS Sender, UM1.FirstName

FROM Request_Master AS RM

LEFT JOIN User_Master AS UM ON RM.Sender_Id = UM.UserId

LEFT JOIN User_Master AS UM1 ON RM.Reciver_Id = UM1.UserId

WHERE UM.UserId = 2 AND UM1.UserId = 2

THEN OUTPUT IS C AND D

BUT NOT WORK THIS Query..

Not Display any data

Maciej Los 28-Jul-14 6:45am

Please, see my answer after update.

Ankit Patel(AP) 28-Jul-14 7:46am

DECLARE @UserMaster TABLE (UserId INT IDENTITY(1,1), UserName VARCHAR(30))

INSERT INTO @UserMaster (UserName)

VALUES('user a'),('user b'),('user c'),('user d')

DECLARE @RequestMaster TABLE (RepId INT IDENTITY(1,1), SenderId INT, Recieverid INT)

INSERT INTO @RequestMaster ( SenderId, Recieverid)
VALUES(1, 2), (3,1)

SELECT RM.RepId, UM.UserName AS Sender, (SELECT UserName FROM @UserMaster WHERE UserId = RM.Recieverid ) AS Reciever
FROM @RequestMaster AS RM
LEFT JOIN @UserMaster AS UM ON RM.SenderId = UM.UserId

WHERE UM.UserId = 3

THEN Output IS : 2 User c User a

But this is Wrong Output

Right Output Is Below
IF Pass UserId = 3

OutPut Is :

User B
User D

AND

IF Pass User_Id = 4

THEN OUTPUT IS :

User A
User B
User C

Ankit Patel(AP) 28-Jul-14 8:30am

Thanks for your help but i did not get my output . Please help me if possible

Thanks for reply

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ankit Patel(AP) · Accepted Answer · 2014-07-31T01:59:00

SQL

SELECT

User_Master.UserId

FROM User_Master

LEFT JOIN Request_Master ON User_Master.UserId = Request_Master.Reciver_Id

AND User_Master.UserId = Request_Master.Sender_Id

WHERE User_Master.UserId NOT IN

(SELECT Request_Master.Reciver_Id FROM Request_Master WHERE Request_Master.Sender_Id = 15

UNION ALL

SELECT Request_Master.Sender_Id FROM Request_Master WHERE Request_Master.Reciver_Id = 15)

AND User_Master.UserId != 15

How to Get Data from master table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to Get Data from master table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us