How to modify SQL to make * = all in join query

[mrchonginhk]

I have Table A like this

Country, Product, Sales
==========
US, A, 100
UK, A, 200
US, B, 150
UK,B, 350

In Table A it is like this

Country, Product, Rate
===============
US, A, 0.9
UK, *, 0.7

I want to join these 2 tables if both Country and Product are same as those in Table B, so that Rate is added to right side of Table A,
and my challenge here is that I need the "*" will select all Products, and so expected outcome is:

Country, Product, Sales, Rate
==========
US, A, 100, 0.9
UK, A, 200, 0.7
UK, B, 350, 0.7

What should be the jet SQL ?
Pls help, thanks

That "*" can also be something else like % etc.

 

[xenou]

One solution is to generate the value for UK on the fly. So here I have called your first table Table1 and the second table Table2.

With this approach, queries 1 and 2 work together with query 3 to create a list of all the rates without the asterisk or wildcard values (using products for the country in question as found in Table 1).

Then we can run the query that is needed using this expanded list.


Query001:

select distinct Country, Product 
from TableA

Query002:

select distinct Country, Rate 
from TableB 
where Product = '*'

Query003:

select q2.Country, q1.Product, q2.Rate 
from 
	Query002 q2 
	inner join Query001 q1 
	on q1.Country = q2.Country

union

select Country, Product, Rate 
from TableB 
where Product <> '*'

Query004:

select 
	t1.Country, t1.Product, t1.Sales, q3.Rate
from 
	Query003 q3 
	inner join TableA t1 
	on q3.Country = t1.Country
	and q3.Product = t1.Product

Just to be clear, Query003 generates the pseudo table as below:

----------------------------
| Country | Product | Rate |
----------------------------
| UK      | A       |  0.7 |
| UK      | B       |  0.7 |
| US      | A       |  0.9 |
----------------------------

and then Query004 generates the result:

------------------------------------
| Country | Product | Sales | Rate |
------------------------------------
| US      | A       |   100 |  0.9 |
| UK      | A       |   200 |  0.7 |
| UK      | B       |   350 |  0.7 |
------------------------------------

In SQL Server I would probably combine all this into one query with subqueries, but with MSAccess it generally seems to work better to break subqueries up into separate saved queries that can call each other.