exclude duplication - 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)

exclude duplication

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2010-06-07 : 16:58:19

Hi All.
I join three tables Customer(Customer_Id,FName, LName); CustomerVisit(CustomerVisit_Id, Customer_Id, Date, SvcNo); VisitProcedure(VisitProcedure_Id, CustomerVisit_Id, Quantity)

select distinct
cv.CustomerVisit_Id
,convert(varchar(10),Date,101) Date
,Svc_No
,FName + ' ' + LName CustomerName
,c.Customer_Id
,Doctor_Id
,Quantity
from dbo.Customer c
join dbo.CustomerVisit cv
on c.Customer_id = cv.Customer_Id
left outer join dbo.VisitProcedure vp
on cv.CustomerVisit_Id = vp.CustomerVisit_Id

My problem is when same Customer has more then one procedure per visit I have duplication.
How to recreate that SELECT statement to exclude duplication?
Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 03:33:30

How to decide which VisitProcedure should be in the resultset?
min(VisitProcedure_Id) or something else?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources