Re: NULLS in union of two tables

Nuno Guerreiro (nuno-v-guerreiro_at_telecom.pt) wrote:
: On 2 Jun 1998 14:26:43 GMT, jperonto_at_engr.latech.edu (Julie A.
: Peronto) wrote:

: >I am creating a view that is the union of two tables that hold different
: >information about like objects. There are some fields that exist in
: >both tables & other fields that exist in one or the other table. The
: >union will hold a combination of the two. For example, if the tables
: >are called table_1 & table_2, then both table_1 & table_2 have a field
: >called wr_no, table_1 has a field called wr_type, and table_2 has a
: >field called wr_status. wr_type & wr_status are not related to each
: >other in any way. I want to create a view that unions the two tables in
: >such a way that the view has the fields wr_no, wr_type, wr_status.
: >

: I believe what you need to perform is a JOIN, not a UNION. I think the
: following command will create the view you need:

: CREATE VIEW v1 AS
: SELECTa.wr_no, a.wr_type, b.wr_status
: FROM table_1 a, table_2 b
: WHERE a.wr_no=b.wr_no;

: Hope this helps

: Nuno Guerreiro
:

Thanks for the response, but I guess I should have been more clear. While the information in the tables pertains to like objects, the tables are mutually exclusive in those objects. The view will bring together all information about all objects. Going on my previous example, if the data in the two tables were as follows:

table_1			table_2
wr_no	wr_type		wr_no	wr_status
123	'WORK'		234	'GOOD'

I would want the resulting view to be:

VIEW

wr_no	wr_type		wr_status
123	'WORK'		NULL
234	NULL		'GOOD'

Both selects require a NULL value as a placeholder for information not in the one table but in the other table, leading to my original posted problem.

Julie Received on Tue Jun 02 1998 - 12:05:44 CDT