如何从主表中获取数据 [英] 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屋!