Re: getting a value from a second table if the first table has not matching records

<frogman042_at_yahoo.com> wrote in message news:1149679162.011077.217110_at_j55g2000cwa.googlegroups.com...
:I have two tables that both contain names. There is no common index

i believe you mean no pk/fk relationships

: between the two tables (they are independent of each other). Both
: tables have 'name' fields, for example table TA has a column called
: a_name and table TB has a column called b_name.
:
: What I'm trying to do is to search table A for a name matching a
: specific value, if that does NOT match any records in table A, I want
: to see if there are any records in table B that match. Whichever
: matches, I would like the name and some other associated columns
: returned with a common name, e.g. final_name.
:

what if the name is found in both tables?

: I've tried experementing with with a select case but without luck
:
: I've tried sql like:
:
: Select case when a.a_name is not null then a.a_name
: else b.b_name ' I've also tried a select
: in here as well as a when clause
: from TA a, TB b
: when a.a_name = 'testname' or b.b_name = 'testname'

it's always best to post SQL that actual runs, but this gives a little bit of an idea of what you've tried

:
: Any ideas?
:
: TIA
:
: ---Jay
:

you could use a UNION, but you are also close with your "a.a_name = 'testname' or b.b_name = 'testname'" clause (assuming you actually used with WHERE and not when)

a statement like this seems to be what you are looking for:

select nvl(a.name,b.name)
from a, b
where a.name = 'namevalue'
or b.name = 'namevalue'

but if you already know the name, you really don't need the NVL -- with bind variables you could do

select :nameval
from a, b
where a.name = :nameval
or b.name = :nameval

how about if you post complete SQL statements (and any error messages received) plus your database version, and also what tool you are using to execute the SQL and/or deploy it to production?

++ mcs Received on Wed Jun 07 2006 - 06:48:57 CDT