Select from another table if first resultset is empty

[#1]

I have an issue where I am doing a left join on two tables, but if the second table doesn't contain a match, I would like to look in another table.  Example:

LOAD "sod_acct",
"sod_nbr" as "so_nbr",
"sod_domain" as "Domain",
"sod_cc",
"sod_confirm",
"sod_consignment",
"sod_covered_amt",
"sod_um_conv";
SQL SELECT *
FROM DELMFG.PUB."sod_det";
LEFT JOIN
LOAD "ad_city",
UPPER("ad_addr") as "ShipNo",
"ad_domain" as "Domain",
"ad_name" as "ship_to_name",
"ad_state" as "ship_to_state",
"ad_type",
"ad_zip" as "ship_to_zip";
SQL SELECT *
FROM DELMFG.PUB."ad_mstr" where ad_type = 'ship-to';

Basically, if there is not a match in ad_mstr, I would like to look into another table and see if I can find a match in that table.

Anybody have a suggestion?

[Anonymous]

First, you can use Inner Join between first and second table.  After that, check the number of records.  If there are >0 records, that means there are matches, use your script.  If there are no records in the inner join, then join another table.

A simple app is attached.

[Anil_Babu_Samineni]

I didn't find any field name called ship_to. Where you do you taken that field name from table 1. Can you please describe more