Re: include NULL from a link?

How abou this?

select c.cust_nmbr, c.name, c.city, c.state, c.zip, s.name   from cust_addr c, state s
 where c.state = s.abbrev
union all
  select c.cust_nmbr, c.name, c.city, c.state, c.zip, null   from cust_addr c
 where c.state is null

chris.

--
Christopher Beck, Principal Technologist, Oracle Corporation
christopher.beck_at_oracle.com
Beginning Oracle Programming,
http://www.amazon.com/exec/obidos/ASIN/186100690X



"cyburgh" <cyburgh_at_yahoo.dot.com> wrote in message
news:3D19C716.1A2100EA_at_yahoo.dot.com...


> Running Oracle 9i DB

> using Discoverer 4

>

> We have many master tables and many detail tables with many links.

> Here's simple example of my problem.

>

> STATE (master table):  column 1 is state abbrev & col 2 is the state

>

> AK |  Alaska

> AL  |  Alabama

> AR  | Arkansas

> CA |  California

> etc

> etc

>

> CUST_ADDR  (detail table): col 1 - cust nmbr, col 2 - name, col 3 -

> city, col 4 - state, col 5 - zip.

>

> cust1|Jones|1 Main St.|San Fran|CA|12345

> cust2|Smith|First St|Los Ang|CA|54321

> cust3|Xiang|1 Holly St|Hong Kong|(null)|00101

>

> Without doing an outer join - how can a user have 1 Discoverer Query

> return all 3 customer records that would look something like:

>

> cust1|Jones|1 Main St.|San Fran|CA|12345|California

> cust2|Smith|First St|Los Ang|CA|54321|California

> cust3|Xiang|1 Holly St|Hong Kong|(null)|00101|(null)

>

> thanks

>