Chedck the query.
create table #tbluserinfo(id int ,First_Name varchar(100), EMAIL VARCHAR(40))
create table #tblfrequst(f_id int,to_id int, from_id int,request_status VARCHAR(10))
create table #STORY (id int , U_id int,file_path VARCHAR(100))
insert into #tbluserinfo values(10,'SHIVANI','SHIVANIGARG@GMAIL.COM')
insert into #tbluserinfo values(6,'Sanjay','sanjay@GMAIL.COM')
insert into #tbluserinfo values(5,'Mohit','Mohitbatta@gmail.COM')
insert into #tbluserinfo values(9,'RAMAN','ramanbansal@gmail.com')
insert into #tblfrequst values(1,5,10,'AC')
insert into #tblfrequst values(3,9,10,'AC')
insert into #tblfrequst values(4,10,6,'AC')
INSERT INTO #STORY VALUES(1,5,'~/Story/WP_20140331_004.jpg')
INSERT INTO #STORY VALUES(2,5,'~/Story/WP_20140413_001.jpg')
INSERT INTO #STORY VALUES(3,5,'~/Story/WP_20140331_020.jpg')
INSERT INTO #STORY VALUES(4,5,'~/Story/WP_20140408_007.jpg')
INSERT INTO #STORY VALUES(5,6,'~/Story/20171022_191900.jpg')
INSERT INTO #STORY VALUES(6,6,'~/Story/20171025_174032.jpg')
INSERT INTO #STORY VALUES(7,6,'~/Story/20171027_205850.jpg')
INSERT INTO #STORY VALUES(8,6,'~/Story/20171101_110004.jpg')
INSERT INTO #STORY VALUES(9,6,'~/Story/20171101_110004.jpg')
INSERT INTO #STORY VALUES(10,10,'~/Story/20150417_190539.jpg')
INSERT INTO #STORY VALUES(11,10,'~/Story/20150503_095829.jpg')
INSERT INTO #STORY VALUES(12,10,'~/Story/20150417_190419.jpg')
INSERT INTO #STORY VALUES(13,9,'~/Story/DSC00636.JPG')
INSERT INTO #STORY VALUES(14,9,'~/Story/DSC00637.JPG')
INSERT INTO #STORY VALUES(15,9,'~/Story/DSC00638.JPG')
DECLARE @Id INT
SET @Id = 5
SELECT TOP 1 *
FROM
(
select a.to_id, a.from_id, b.*, b.id as 'bid',c.file_path, c.u_id as 'cuid'
from #tblfrequst a
join #tbluserinfo b on a.to_id=b.id
join #story c on c.u_id=b.id
where a.request_status='AC'
UNION
select a.to_id, a.from_id, b.*, b.id,c.file_path, c.u_id
from #tblfrequst a
join #tbluserinfo b on a.from_id=b.id
join #story c on c.u_id=b.id
where a.request_status='AC'
)story
WHERE id=@Id
ORDER BY ROW_NUMBER() OVER (PARTITION BY cuid ORDER BY cuid DESC)
DROP TABLE #tbluserinfo
DROP TABLE #tblfrequst
DROP TABLE #STORY
Output
| to_id |
from_id |
id |
First_Name |
EMAIL |
bid |
file_path |
cuid |
| 5 |
10 |
5 |
Mohit |
Mohitbatta@gmail.COM |
5 |
~/Story/WP_20140331_004.jpg |
5 |
