Need help with a query that includes an outer join and just confuses me

From: kay <themonkeygirl_at_excite.com>
Date: 12 Feb 2003 20:21:39 -0800
Message-ID: <31ef0a3d.0302122021.633d1aff_at_posting.google.com>

I've read the oracle reference book, but still don't understand how to do this exactly. Any advice is appreciated. I have another reference book coming in the mail, but I need this ASAP.

person table has
country
region
state
city

place table has the following fields
country
co_flag
region
r_flag
state
s_flag
city
c_flag

If a person lives in a place in the place table and any of the flags are set, that returns true. But, the person table can have null city, state, region or country. There are no nulls in the place table, so it needs to know that if the country and region match and either flag is set, return true, or if the country, region and state match and any flag is set, return true, or if all four match and any flag is set return true.

This is what I tried that doesn't work... I'm not sure about how putting paramaters on outer joined tables works exactly...

...place.country=person.country -- no outer join needed here

and place.region=person.region(+)
and place.state=person.state(+)
and place.city=person.city(+)

and (person.co_flg='y' or
(place.region=person.region and person.r_flg(+)='Y')

or (place.region=person.region and place.state=person.state and
person.s_flag(+)='Y')
or (place.region=person.region and place.state=person.state and

place.city=person.city and person.c_flag(+)='Y')

What is the smart way to do this? Book recommendations are also appreciated : )

Thanks so much Received on Thu Feb 13 2003 - 05:21:39 CET