Want an unique result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Want an unique result

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-01 : 17:45:30

Hello,

I have a query below

select A.CODEDESC,L.TRAN_ID
FROM REASONCODES A  right join LOAN_PICKUP L
on L.REAS_CD= A.CODEID
inner join TRANS_HEADER T
on A.CODEGROUP = T.TRAN_CD
where T.TRAN_CD=22

I get the result which likes,
CODEDESC TRAN_ID

SUPPLIES	3211724
SUPPLIES	3217348
TRANSFER  	3215784
SUPPLIES	3211724
SUPPLIES	3217348
TRANSFER  	3215784
SUPPLIES	3211724
SUPPLIES	3217348
TRANSFER  	3215784
.....

They are repeated, why?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 17:57:40

They are repeated because there's multiple matches in the joins.

You could use DISTINCT or GROUP BY/aggregate function to get rid of the "dupes".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-01 : 18:06:25

[code]select DISTINCT L.TRAN_ID,A.CODEDESC
FROM REASONCODES A right join LOAN_PICKUP L
on L.REAS_CD= A.CODEID
inner join TRANS_HEADER T
on A.CODEGROUP = T.TRAN_CD
where T.TRAN_CD=22[/code]
is fine. I only want CODEDESC.But
[code]SELECT CODEDESC FROM (select DISTINCT L.TRAN_ID,A.CODEDESC
FROM REASONCODES A right join LOAN_PICKUP L
on L.REAS_CD= A.CODEID
inner join TRANS_HEADER T
on A.CODEGROUP = T.TRAN_CD
where T.TRAN_CD=22)[/code] is wrong.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 18:11:37

I don't follow you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-01 : 18:18:16

I mean if I adopt your suggestion, the query

select DISTINCT L.TRAN_ID,A.CODEDESC
FROM REASONCODES A  right join LOAN_PICKUP L
on L.REAS_CD= A.CODEID
inner join TRANS_HEADER T
on A.CODEGROUP = T.TRAN_CD
where T.TRAN_CD=22

resluts in
the result as
TRAN_ID CODEDESC

3211724	SUPPLIES
3215784	TRANSFER
3217348	SUPPLIES

However I only want CODEDESC, therefore I used the following query

SELECT CODEDESC FROM (select DISTINCT L.TRAN_ID,A.CODEDESC
FROM REASONCODES A  right join LOAN_PICKUP L
on L.REAS_CD= A.CODEID
inner join TRANS_HEADER T
on A.CODEGROUP = T.TRAN_CD
where T.TRAN_CD=22)

Then an error occuies
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 18:56:55

You need an alias for that derived table.

SELECT CODEDESC FROM (select DISTINCT L.TRAN_ID,A.CODEDESC
FROM REASONCODES A right join LOAN_PICKUP L
on L.REAS_CD= A.CODEID
inner join TRANS_HEADER T
on A.CODEGROUP = T.TRAN_CD
where T.TRAN_CD=22) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources