Select records without duplicate in Join query in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

Hi!

Result shows with duplicate, how to ignore duplicate.

I used two tables get result. I separated money by two types 0 and 1. When I run its shows money by two parameters. Now I want result by 0 parameter. 

declare @Students table (IdStd char(5), LastName nchar(25), IdSpec char(10), StudYear char(7), StdActive int, StudType int, TypeStud int)
insert into @Students values ('0001A', 'Pulodov Rustam', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0002A', 'Odinaev Komron', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0003A', 'Nasimov Shohrukh', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0004A', 'Abdurahmonov Muhammad', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0006A', 'Davlatov Odinamad', '2-240101', '2020/21', 1, 1, 2)
--select * from @Students
declare @Cash table (Id int, SMoney money, IdSpec char(10), IdStd char(5), Pay int, Years char(7))
insert into @Cash values (1, 2000, '2-240101', '0001A', 0, '2020/21')
insert into @Cash values (2, 3000, '2-240101', '0003A', 0, '2020/21')
insert into @Cash values (3, 1500, '2-240101', '0002A', 0, '2020/21')
insert into @Cash values (4, 1000, '2-240101', '0001A', 0, '2020/21')
insert into @Cash values (5, 1500, '2-240101', '0002A', 0, '2020/21')
insert into @Cash values (6, 1500, '2-240101', '0006A', 0, '2020/21')
insert into @Cash values (7, 800, '2-240101', '0006A', 1, '2020/21')
insert into @Cash values (8, 1500, '2-240101', '0001A', 0, '2020/21')
insert into @Cash values (9, 500, '2-240101', '0001A', 1, '2020/21')
SELECT st.[IdStd], ltrim(rtrim(st.LastName))LastName, case c.Pay when 0 then SUM(c.[SMoney]) end'SMoney', st.IdSpec, st.TypeStud, st.StudType, c.Years
FROM @Cash c right join @Students st on c.IdStd = st.IdStd group by c.Years, st.IdStd, st.LastName, c.Pay, st.IdSpec, st.TypeStud, st.StudType, c.Years

I want this result:

IdStd

LastName

SMoney

IdSpec

TypeStud

StudType

Years

0004A

Abdurahmonov Muhammad

NULL

2-240101

1

1

NULL

0001A

Pulodov Rustam

4500,00

2-240101

1

1

2020/21

0002A

Odinaev Komron

3000,00

2-240101

1

1

2020/21

0003A

Nasimov Shohrukh

3000,00

2-240101

1

1

2020/21

0006A

Davlatov Odinamad

1500,00

2-240101

2

1

2020/21

You are viewing reply posted by: dharmendr 3 months ago.
Posted 3 months ago Modified on 3 months ago

Hi PRA,

Refer below query.

SQL

declare @Students table (IdStd char(5), LastName nchar(25), IdSpec char(10), StudYear char(7), StdActive int, StudType int, TypeStud int)
insert into @Students values ('0001A', 'Pulodov Rustam', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0002A', 'Odinaev Komron', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0003A', 'Nasimov Shohrukh', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0004A', 'Abdurahmonov Muhammad', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0006A', 'Davlatov Odinamad', '2-240101', '2020/21', 1, 1, 2)
--select * from @Students
declare @Cash table (Id int, SMoney money, IdSpec char(10), IdStd char(5), Pay int, Years char(7))
insert into @Cash values (1, 2000, '2-240101', '0001A', 0, '2020/21')
insert into @Cash values (2, 3000, '2-240101', '0003A', 0, '2020/21')
insert into @Cash values (3, 1500, '2-240101', '0002A', 0, '2020/21')
insert into @Cash values (4, 1000, '2-240101', '0001A', 0, '2020/21')
insert into @Cash values (5, 1500, '2-240101', '0002A', 0, '2020/21')
insert into @Cash values (6, 1500, '2-240101', '0006A', 0, '2020/21')
insert into @Cash values (7, 800, '2-240101', '0006A', 1, '2020/21')
insert into @Cash values (8, 1500, '2-240101', '0001A', 0, '2020/21')
insert into @Cash values (9, 500, '2-240101', '0001A', 1, '2020/21')

;WITH CTE AS (
SELECT st.[IdStd], ltrim(rtrim(st.LastName))LastName,
case c.Pay when 0 then SUM(c.[SMoney]) end 'SMoney', st.IdSpec, st.TypeStud, st.StudType, c.Years
FROM @Cash c 
LEFT OUTER join @Students st on c.IdStd = st.IdStd
WHERE c.Pay=0
group by c.Years, st.IdStd, st.LastName, c.Pay, st.IdSpec, st.TypeStud, st.StudType, c.Years
)

SELECT * FROM CTE

UNION
SELECT IdStd,LastName,NULL,IdSpec,TypeStud,StudType,NULL
FROM @Students
WHERE IdStd NOT IN (SELECT IdStd FROM CTE)
ORDER BY [SMoney]

Output

IdStd LastName SMoney IdSpec TypeStud StudType Years
0004A Abdurahmonov Muhammad NULL 2-240101 1 1 NULL
0001A Pulodov Rustam 4500 2-240101 1 1 2020/21
0002A Odinaev Komron 3000 2-240101 1 1 2020/21
0003A Nasimov Shohrukh 3000 2-240101 1 1 2020/21
0006A Davlatov Odinamad 1500 2-240101 2 1 2020/21