RE: SQL Question

Thank you,  The union was what I was looking for...  If by chance my field
length are different, can I substring to make them match?

Thanks again, tim

> -----Original Message-----
> From: Buck Calabro [SMTP:Buck.Calabro@xxxxxxxxxxxx]
> Sent: Friday, March 07, 2003 1:57 PM
> To:   Midrange Systems Technical Discussion
> Subject:      RE: SQL Question
> 
> 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
> _______________________________________________
> 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.
This e-mail message, including any attachments, is for the sole use of the
intended recipient(s) and may contain confidential or privileged
information.  Any unauthorized review, use, disclosure or distribution is
prohibited.  If you are not the intended recipient, please contact the
sender by reply e-mail and destroy the message.