SQL query fetching duplicate rows

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

General SQL Server Forums

New to SQL Server Programming

SQL query fetching duplicate rows

Author

Topic

Venu@wfis
Starting Member

16 Posts

Posted - 2009-06-26 : 05:27:33

Hi below is a simple SQL query. I joined 5 tables to get the values. Problem is when i execute the duplicate rows are being displayed.

When i Say 'distinct' or use 'UNION' it says "The text, ntext, or image data type cannot be selected as DISTINCT."

Is there any other way aroiund to eliminate the duplicate rows. Thank you.

select
e.al_description as [Subject],
a.rq_user_09 as [ReqID],
a.rq_req_name as [ReqName],
a.rq_req_comment as [ReqDescription],
a.rq_req_Priority as [ReqPriority],
c.ts_name as [TestName],
c.ts_type as [TestType],
c.ts_user_07 as [TestPriority],
d.tc_status as [ExecutionStatus]
from td.test c
inner join td.req_cover b on b.rc_entity_id =c.ts_test_id
inner join td.req a on a.rq_req_id=b.rc_req_id
inner join td.testcycl d on d.tc_test_id=c.ts_test_id
inner join td.all_lists e on c.ts_subject=e.al_item_id
where e.al_item_id = 4877

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-26 : 05:30:42

hi, use group by

select
e.al_description as [Subject],
a.rq_user_09 as [ReqID],
a.rq_req_name as [ReqName],
a.rq_req_comment as [ReqDescription],
a.rq_req_Priority as [ReqPriority],
c.ts_name as [TestName],
c.ts_type as [TestType],
c.ts_user_07 as [TestPriority],
d.tc_status as [ExecutionStatus]
from td.test c
inner join td.req_cover b on b.rc_entity_id =c.ts_test_id
inner join td.req a on a.rq_req_id=b.rc_req_id
inner join td.testcycl d on d.tc_test_id=c.ts_test_id
inner join td.all_lists e on c.ts_subject=e.al_item_id
where e.al_item_id = 4877
group by e.al_description,a.rq_user_09,a.rq_req_name,a.rq_req_comment,
a.rq_req_Priority,c.ts_name,c.ts_type,c.ts_user_07,d.tc_status

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 05:34:01

I would investigate which table that produces the duplicate information.
And then I would deal with the business rules which dictates what to do in this scenario.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources