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

From: <frogman042_at_yahoo.com>
Date: 7 Jun 2006 04:19:22 -0700
Message-ID: <1149679162.011077.217110@j55g2000cwa.googlegroups.com>

I have two tables that both contain names. There is no common index 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.

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'

Any ideas?

TIA ---Jay Received on Wed Jun 07 2006 - 06:19:22 CDT