Display record using Join query without duplicate row in SQL Server

ashish007
 
on May 23, 2021 11:11 PM
419 Views

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)
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 24, 2021 07:31 AM

Hi ashish007,

Use DITINCT keyword in the SELECT statement.

select DISTINCT p.id,p.first_name,pt.*--,te.image_url
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

Remove the image_url selection from the select query. Else it will be displayed each pid with each image.

You can write separate select query to select the images and display as required.