On Thu, Feb 28, 2008 at 7:43 AM, Helen Borrie <helebor@...> wrote:

> 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.

Hi Helen,

I shouldn't agree more. After reading your book on UNIONS, I have to
agree with you.
I have a few introductory books on SQL, and a lot of books on
database design. I was never really a DBA, nor have I really worked
that much as an SQL programmer. There was always someone writing the
SQL. And, I have only started to look into SQL, since I am actually
taking some time to learn it. I have been using Firebird since it was
Interbase 5.5, but never actually wrote any complex SQL. I have always
used it for testing. The DBA would then write the efficient SQL.

Your book is really very clear and succinct. I should really take more
time to read it as opposed to using it as a reference only. I keep
forgetting to refer back to it. I have ordered some books on advanced
SQL, so that I stop asking stupid questions. :D

Thanks once again for taking the time to reply to my e-mail, and for
providing me with a very clear example.

All the best,

Fidel.