Re: HELP - NVL or DECODE

I have the three following tables

1. people per_id per_email per_tel per_addr
2. organizations org_id org_email org_tel org_addr
3. employees (this is for the many-to-many relations between the people & organizations table) per_id org_id

select peo.per_email, peo.per_tel, peo.per_addr from people peo, organizations org, employees emp
where peo.per_id=emp.emp_per_id(+)
and emp.emp_org_id=org.org_id(+)

lets assume for the example that there is a person for every organization

What i want is if peo.per_email is null then give me the value of org.org_email,
the same for peo.per_tel and peo.per_addr, in other words if a person's email, tel or address is not registered than have the corresponding details for his company.

thanks in advance
zak

Mark D Powell <markp7832_at_my-deja.com> wrote in message news:8ubnpv$jdh$1_at_nnrp1.deja.com...
> In article <8ub9jg$1ibo$1_at_ulysses.noc.ntua.gr>,
> "zak" <zak_at_intrafunk.com> wrote:
> > Hi all,
> >
> > i've got two tables (people, organizations) and one more to link
 these two
> > in a many-to-many relation. I want to query the "people" table and
 when
> > there is no data found (null values) in the columns (email, tel,
 address), i
> > want to have their organizations contact (email, tel, address) data.
> >
> > thanks in advance
> > zak
> >
> Zak, I think it would help if you posted your query. Where is the data
> that you want that may be null coming from? Is it in the people table
> or in the organization table?
>
> You might want an outer join where you always get the row from the
> people table and null values for organization when there is not
> matching organization row OR
>
> You may want the result of selecting all rows from people where the
> people row is not in a join of people to organization, i.e., the result
> of an anti-join.
>
> I can't tell from your initial post. Perhaps someone else will read it
> different and post your solution.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Nov 08 2000 - 08:49:16 CST