How to data display using join without duplicate row
ex. shivani and mohit friend. shivani upload 4 post. and upload 8 photo. run below query.
output show total 32 no of row. i want the out put show 8 no row.
when i have run the below query without join post table after that output correct show.
select p.id,p.first_name,te.image_url,pt.*
from tbluserinfo as p
join tbluserprofilepicture t on p.id=t.id
join post pt on pt.id=p.id
join tblfrequst tbf on tbf.to_id=p.id
join CREATE_ALBUM pc on pc.U_ID=p.id
join ALBUM_PHOTO te on te.al_id=pc.AL_ID
where tbf.from_id=5
union
select p.id,p.first_name, te.image_url,pt.*
from tbluserinfo as p
join tbluserprofilepicture t on p.id=t.id
join post pt on pt.id=p.id
join tblfrequst tbf on tbf.from_id=p.id
join CREATE_ALBUM pc on pc.U_ID=p.id
join ALBUM_PHOTO te on te.al_id=pc.AL_ID
where tbf.to_id=5
create table tbluserinfo(id int identity(1,1),First_Name varchar(100)) -- 1 table
create table tbluserprofilepicture(id int, image_name varchar(100)) --2 table
create table post(pid int identity(1,1),post_text varchar(max), id int) -- 3 table
create table tblfrequst(f_id int identity(1,1),to_id int, from_id int))--4 table
CREATE TABLE create_Album([al_id] [int] IDENTITY(1,1) NOT NULL,[al_name] [varchar](100) NULL,[u_id] [int] NULL) -- 5 table
CREATE TABLE album_photo([wl_id] [int] IDENTITY(1,1) NOT NULL,[image_url] [varchar](100) NULL,[al_id] [int] NULL)--6 table
insert into tbluserinfo values(10,'SHIVANI','SHIVANIGARG@GMAIL.COM') -- 1 table record
insert into tbluserinfo values(5,'Mohit','Mohitbatta@gmail.COM')
insert into tbluserprofilepicture values(10,'10.jpg')--2 table record
insert into tbluserprofilepicture values(5,'5.jpg')
insert into post values(1,'hi',10) -- 3 table record
insert into post values(2,'hello',10)
insert into post values(3,'kya bat hai',10)
insert into post values(4,'kya hall hai',10)
insert into tblfrequst values(1,5,10,'AC') -- 4 table record
insert into create_album values(1,'dil',10) -- 5 table record
insert into create_album values(2,'Matrix',5)
insert into album_photo values(1,'userimage/1.jpg',1) --6 table record
insert into album_photo values(2,'userimage/2.jpg',1)
insert into album_photo values(3,'userimage/3.jpg',1)
insert into album_photo values(4,'userimage/4.jpg',1)
insert into album_photo values(5,'userimage/5.jpg',1)
insert into album_photo values(6,'userimage/6.jpg',1)
insert into album_photo values(7,'userimage/7.jpg',1)
