如何搜索SQL Server中发送的所有好友请求 [英] How to search all friend request sent in SQL server
问题描述
Hello friens,
我需要帮助加入sql。我有2表第1是tbl_register amd第2是请求。
Tbl_register
UserID f_name Designationnm Comapny_name
1 aaa CEO Apple
2 Rajeev总经理戴尔
3 upendra董事总经理戴尔
4 soumendr副总裁lenovo
9 Suneal董事总经理Apple
8 asa CEO Apple
请求表
req_from_id req_to_id req_status
2 9 0
3 9 0
4 9 0
2 8 0
2 1 0
i需要得到这样的输出。
f_name Designationnm Comapny_name UserID req_from_id
aaa aa CEO Apple 1 2
Rajeev总经理戴尔2 9
Suneal董事总经理Apple 9 2
asa首席执行官Apple 8 2
我的尝试:
选择不同的
tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,tb2.req_from_id
来自Tbl_register tb1
左加入
tbl_friend_and_match_request tb2
on
tb1.UserID = tb2.req_from_id
其中
Comapny_name喜欢'%a% '或者像'%Manager'那样的Designationnm
它提供了重复项,因为这就是你告诉它的目的。
如果你改变它以便它返回req_to_id
而不是req_from_id
那么它显然是为什么:
SELECT r.f_name,r.Designationnm,r.Comapny_name,r.UserID,f.req_to_id
FROM tbl_Register r
LEFT JOIN tbl_friend_and_match_request f ON f.req_from_id = r.UserID
其中 Comapny_name 喜欢 ' %a%' 或 Designationnm 喜欢 ' %Manager%'因为请求表中有三行与寄存器表中的一行JOIN 。
并且它自己的SQL无法知道你想要哪一个!
f_name Designationnm Comapny_name UserID req_to_id
aaa首席执行官Apple 1 NULL
Rajeev总经理戴尔2 9
Rajeev总经理戴尔2 8
Rajeev总经理戴尔2 1
Suneal常务董事Apple 9 NULL
asa CEO Apple 8 NULL您的表需要重新思考,或者您需要决定SQL在发生这种情况时应如何选择其中一个选项。
< blockquote>选择不同
tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,MAX(tb2.req_from_id)
来自Tbl_register tb1
离开加入
tbl_friend_and_match_request tb2
on
tb1.UserID = tb2.req_from_id
其中
Comapny_name喜欢'%a%'或Designationnm喜欢'%Manager%'
Group By tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name
Hello friens,
i need help with join in sql. i have 2 table 1st is tbl_register amd 2nd is request.
Tbl_register UserID f_name Designationnm Comapny_name 1 aaa CEO Apple 2 Rajeev General manager Dell 3 upendra Managing director Dell 4 soumendr Vice president lenovo 9 Suneal Managing director Apple 8 asa CEO Apple request table req_from_id req_to_id req_status 2 9 0 3 9 0 4 9 0 2 8 0 2 1 0
i need to get output like this.
f_name Designationnm Comapny_name UserID req_from_id aaa aa CEO Apple 1 2 Rajeev General manager Dell 2 9 Suneal Managing director Apple 9 2 asa CEO Apple 8 2
What I have tried:
select distinct tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,tb2.req_from_id from Tbl_register tb1 left join tbl_friend_and_match_request tb2 on tb1.UserID = tb2.req_from_id where Comapny_name like '%a%' or Designationnm like '%Manager%'
It gives duplicates, because that is what you told it to do.
If you change it so that it returnsreq_to_id
instead ofreq_from_id
then it's obvious why:
SELECT r.f_name, r.Designationnm, r.Comapny_name, r.UserID, f.req_to_id FROM tbl_Register r LEFT JOIN tbl_friend_and_match_request f ON f.req_from_id = r.UserID where Comapny_name like '%a%' or Designationnm like '%Manager%'Becuase there are three rows in the request table that JOIN with the one row in the register table.
And there is no way that SQL on it's own has any idea which one you want!
f_name Designationnm Comapny_name UserID req_to_id aaa CEO Apple 1 NULL Rajeev General manager Dell 2 9 Rajeev General manager Dell 2 8 Rajeev General manager Dell 2 1 Suneal Managing director Apple 9 NULL asa CEO Apple 8 NULLYour tables need rethinking, or you need to decide how SQL should "pick" one of the options when this occurs.
Select distinct
tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name,MAX(tb2.req_from_id)
from Tbl_register tb1
left join
tbl_friend_and_match_request tb2
on
tb1.UserID = tb2.req_from_id
where
Comapny_name like '%a%' or Designationnm like '%Manager%'
Group By tb1.UserID,tb1.f_name,tb1.Designationnm,tb1.Comapny_name
这篇关于如何搜索SQL Server中发送的所有好友请求的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!