How to get all friend request sent using Join in SQL server

Question

Hello sir, i need help with join in sql. i have 2 table 1st is tbl_register amd 2nd is request.

 

bl_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 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_to_id
where
Comapny_name like '%apple%' or Designationnm like '%Managing director%'

But it gives me output like this :

UserID  f_name Designationnm  Comapny_name   req_from_id
1   aaa          CEO            Apple           2
3   upendra  Managing director   Dell           NULL
8   asa          CEO            Apple           2
9   Suneal  Managing director   Apple           2
9   Suneal  Managing director   Apple           3
9   Suneal  Managing director   Apple           4