Getting DISTINCT results from a union

#1

I want to create a result set that contains the patient numbers
and episode numbers of patients that have had transactions or
payments in the prior month.

I figured that I would do a union of selects on the transaction
file and the payments file, making them distinct so that I only
get one ptNum, epiNum pair for each patient/episode that had one
or more transactions or payments.

But, when I do the union I might (and usually will) end up with a
2nd ptNum, epiNum for each patient/episode. While I can program
around this in the application I was wondering if I could do this
in MySQL so that I ended up with just one ptNum, epiNum pair.

-- Bill Drescher william {at} TechServSys {dot} com

#2

On Nov 13, 12:24*pm, bill <will... (AT) TechServSys (DOT) com> wrote:

Quote:

I want to create a result set that contains the patient numbers
and episode numbers of patients that have had transactions or
payments in the prior month.

I figured that I would do a union of selects on the transaction
file and the payments file, making them distinct so that I only
get one ptNum, epiNum pair for each patient/episode that had one
or more transactions or payments.

But, when I do the union I might (and usually will) end up with a
2nd ptNum, epiNum for each patient/episode. *While I can program
around this in the application I was wondering if I could do this
in MySQL so that I ended up with just one ptNum, epiNum pair.

-- Bill Drescher william {at} TechServSys {dot} com

A UNION is DISTINCT by default. You have to code UNION ALL to stop it
being DISTINCT.

#3

On Nov 13, 7:35*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On Nov 13, 12:24*pm, bill <will... (AT) TechServSys (DOT) com> wrote:

I want to create a result set that contains the patient numbers
and episode numbers of patients that have had transactions or
payments in the prior month.

I figured that I would do a union of selects on the transaction
file and the payments file, making them distinct so that I only
get one ptNum, epiNum pair for each patient/episode that had one
or more transactions or payments.

But, when I do the union I might (and usually will) end up with a
2nd ptNum, epiNum for each patient/episode. *While I can program
around this in the application I was wondering if I could do this
in MySQL so that I ended up with just one ptNum, epiNum pair.

-- Bill Drescher william {at} TechServSys {dot} com

A UNION is DISTINCT by default. You have to code UNION ALL to stop it
being DISTINCT.

Gee, that was easy !:-)
Thanks.
bill

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Getting DISTINCT results from a union

comp.databases.mysql comp.databases.mysql

Getting DISTINCT results from a union - 11-13-2010 , 06:24 AM

Re: Getting DISTINCT results from a union - 11-13-2010 , 06:35 AM

Re: Getting DISTINCT results from a union - 11-14-2010 , 05:01 AM