If you want to use it on an update statement you can also change the CTE
to an inline CTE like this:



select a.* from miscchg A

JOIN (Select Distinct ashpnbr,ABLDNGD from miscchgtmp where....) B ON
a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD



WHERE ...



date: Tue, 27 Nov 2012 11:29:34 -0500

from: Jeff Young <jyoung0950@xxxxxxxxx>

subject: Re: SQL join with distinct



Joel,

Try using a CTE to get miscchgtmp having distinct values, then join to
the

cte,

With Distinct_miscchgtmp as (Select Distinct ashpnbr,ABLDNGD from

miscchgtmp where....)

select a.* from miscchg A, distinct_miscchgtmp B

where a.ASHPNBR = b.ashpnbr

and a.ABLDNGD = b.ABLDNGD





Chris Hiebert
Programmer/Analyst

Disclaimer: Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company.







Return to Archive home page | Return to MIDRANGE.COM home page