Re: SQL join with distinct

[Jeff Young <jyoung0950@xxxxxxxxx>]

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


On Tue, Nov 27, 2012 at 11:24 AM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

> I would like to run the following, but reduce the "B" file down to
> distinct key values PRIOR to the join.
>
> Can this be accomplished in one SQL step?
>
>
>
> select a.* from miscchg A, miscchgtmp B
> where a.ASHPNBR = b.ashpnbr
>   and a.ABLDNGD = b.ABLDNGD
>
>
>
> Maybe using "IN"?  But that only works with one column, not two as I need.
>
> Thanks!
>
>
>
> ______________________________________________________________________
> This outbound email has been scanned for all viruses by the MessageLabs
> Skyscan service.
> For more information please visit http://www.symanteccloud.com
> ______________________________________________________________________
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.