At 03:47 PM 28/02/2008, you wrote:

>But, to be honest
>with you, this is the first time I hear this. All the literature I
>have read so far says the exact same thing: that unions are used to
>draw data from two or more tables.

You're possibly not reading the most well-informed literature....if unions are being presented to you as "to draw data from two or more tables". Why I say that is that SQL is about SETS not tables. The key point about unions is that they provide a way to merge sets from different sources...well, two ways, actually, since UNION produces output with no duplications, while UNION ALL produces output with all rows from all subsets.

I'm serious about that "bad literature", by the way. SQL books should not be encouraging you to think in "tables" but in sets. In a relational database a table is nothing but a labelled box for storing things. People learning to work with relational databases should be encouraged to think in sets and to design the shelving according to the sets they're going to need. Ah, but I don't have time to write a sermon today...

> And I have never come across an example that shows [using UNION to merge multiple sets from one table]. So, if it is not asking too much, could you please provide me with an example with a case where this would happen?

Because I'm short on time I'll give you a view definition we use to do the subscription mailout reminders to FF members.

select
pr.person_id,
(select p1.first_names ||' '
|| p1.surname
from person p1
where p1.person_id = pr.person_id),
pr.correspondent_id,
(select c.corp_name from corporation c
where c.corporation_id = pr.correspondent_id),
(select max(fft1.next_due_date) from ff_transaction fft1
where fft1.person_id = pr.person_id
or fft1.person_id = pr.correspondent_id
and fft1.transac_type in (select tt.transac_type from transac_type tt
where tt.transac_group='SUB'))
from person_role pr
where pr.role_prefix = 'CR'

union

select
p.person_id,
p.first_names ||' ' || p.surname,
cast (null as integer),
CAST('-' as varchar(120)),
(select max(fft2.next_due_date) from ff_transaction fft2
where fft2.person_id = p.person_id
and fft2.transac_type in (select tt1.transac_type from transac_type tt1
where tt1.transac_group='SUB'))

from person p
where not exists (
select 1 from person_role pr1
where pr1.person_id = p.person_id
and pr1.role_prefix = 'CR' )
and exists (
select 1 from member m
where m.person_id = p.person_id
and m.terminal_date is null )

We have two sorts of members who pay subscriptions: individual and corporate. Corporate members are companies. A company has a "corporate representative", who is our contact person for that company. Our view goes via a transactions table where all subscription payments are entered, together with a start and end date that's applicable to each payment. We have other kinds of payments in there but, for our mailouts, we only want to know about subscription payments. These have various transaction types that are grouped under 'SUB'.

Corporations and Persons are in different tables but they share the same generator for their PKs, so the Person ID written in the transaction record might be a person or a corporation. We want to target only persons, even though the Person_ID on the actual subscription payment (carrying the Next Due date) might be the corporation_ID.

The UNION gives us the list we want, by first getting the person data for the corporate representatives and then getting the individuals who are not corporate representatives. No duplications. Each mailout is just a matter of querying this view for those whose Next Due date is in the past or will fall within the coming month.

Actually, we use a parameterised SP to perform this query and do other stuff like picking up the email address and also assigning different message bodies, depending on how much overdue they are...and delivering the output to the mail client...but that's not relevant here. What's important is the power of UNION for abstracting diverse sets and binding them into a single output set.

./hb