Efficiently querying a list?

[Anthracyda]

I have 3 tables: Generic, Treaties, TreatyText
simplified down to
Table: Generic
ID Country TaxRate
-- ----------
1 Brazil 15%
2 Australia 10%
3 US 5%

Table: Treaties
-- ----------
ID Payor Payee TaxRate
1 Brazil Australia 10%
2 Australia US 10%

Table: TreatyText
-- ----------
ID Payor Payee Text
1 Australia US 'Blah,Blah'

The users can build a list of Payor and Payee countries, e.g. Payors Brazil and US, Payees Australia, Netherlands

If we have a match Payor-Payee in Treaties, return that rate and, if we also have a match TreatyText, return the relevant id in TreatyText.
If we don't have a match in Treaties, then return the default taxrate for the Country from the Generic table.
Note that we won't necessarily have an entry in TreatyText for every record in Treaties.

The least efficient route is to run a separate query on Treaties against each Payor-Payee combination.If I get a record, then run a query against TreatyText. If I don't get a record, then run a query against Generic. However, if the user gives me 10 potential payors and 5 potential payees, I have a ridiculous number of queries.

If I use an 'IN' clause, I don't see how I track which Payor-Payee combinations did not return records.

Any suggestions would be appreciated.

 

[r937]

select Generic.Country 
     , Treaties.Payee 
     , coalesce(Treaties.TaxRate
               ,Generic.TaxRate) as TaxRate
     , TreatyText.Text
  from Generic
[b]left outer[/b]
  join Treaties
    on Treaties.Payor = Generic.Country
   and Treaties.Payee   in ('Australia','Netherlands')
[b]left outer[/b]
  join TreatyText
    on TreatyText.Payor = Treaties.Payor
   and TreatyText.Payee = Treaties.Payee  
 where Generic.Country  in ('Brazil','US')

r937.com | rudy.ca

 

[Anthracyda]

Thanks, but this illustrates my problem. Because there is no treaty between Brazil and Australia, I don't get a record for Brazil - NULL -15% - NULL