如何从主表中获取数据 [英] How to Get Data from master table

问题描述

大家好，



我的数据库中有两个表1）Usermaster 2）朋友请求主任





在Usermaster内部，我已经为Exmple提交了UserId用户名



UserId用户名

1   ;            A

2               b

3              c

4              d



In Friend Request Master i已经提交了像ReqId SenderId RecieverId的例子



RepId SenderId Recieverid

1         ;      1              2

2         & nbsp;     3              1



如果我输入B比我得到C，D不是B的朋友，如果我检查C而不是我得到B，D不是C的朋友。

如果输入D比我得到A，B，C不是D的朋友..





我怎么能这样做请帮助...

解决方案

初看起来，它应该类似于：

  SELECT  RM.RepId，UM.UserName  AS 发​​件人，UM.UserName  AS 接收方
  FROM  Requestmaster  AS  RM 
  LEFT   JOIN  Usermaster  AS  UM  ON  RM.SenderId = UM.UserId 
  LEFT   JOIN  Usermaster  AS  UM1  ON  RM.ReceiverId = UM.UserId 

以下是工作示例：

  DECLARE   @ UserMaster  表（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 发​​件人，（ SELECT 用户名 FROM   @ UserMaster   WHERE  UserId = RM.Recieverid） AS 接收方
  FROM   @ RequestMaster   AS  RM 
  LEFT   JOIN   @UserMaster   AS  UM  ON  RM.SenderId = UM.UserId 

结果：

 1位用户a b 
 2位用户c用户a 

[/ EDIT]

  SELECT  
 
 User_Master.UserId 
 
  FRO M  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  

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...

解决方案

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

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:

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]

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

这篇关于如何从主表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！