RE: SQL Question

[Buck Calabro <Buck.Calabro@xxxxxxxxxxxx>]

Hi Tim!

>What I would like to do, is create a view, 
>with a new key field, that joins these 2 
>tables into 1 table, for the purpose of my query...  
>here is the kicker...  the field names are not 
>the same...  there both character fields...
>tablea
>~~~~~~~~~
>loc     stock
>add1   4201
>add2   bend, oregon
>
>tableB
>~~~~~~~~
>shiploc   mm
>shipadd1  3907 highland dr
>shipadd2  bellingham, wa

I'm not sure if you want a JOIN or a UNION.  The difference is that a JOIN
will produce a new record format with all the fields from both files in it,
where a UNION will produce a record format with one set of fields.

The JOIN:
select loc,shiploc,add1,shipadd1,add2,shipadd2
  from tablea,tableb
  where loc=shiploc
  order by loc
You can see that you will get a record with 6 fields in it.

The UNION:
select loc,add1,add2 from tablea union
select shiploc,shipadd1,shipadd2 from tableb
  order by 1
In this case, you get a single table with the addresses from TABLEA and
TABLEB mixed together.  If you need to distinguish the records by table,
include a "discriminator" field like so:

select 'A',loc,add1,add2 from tablea union
select 'B',shiploc,shipadd1,shipadd2 from tableb
  order by 1,2
Now, all the records from TABLEA will appear before the records from TABLEB.

The key to UNION is that the SELECT clauses need to exactly match in
datatype column for column.  That is, loc and shiploc must each be
character, add1 and shipadd1 character and so on. 

I hope I gave you something to work with.
  --buck