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

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
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.
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,612
Office Version
2013
Platform
Windows
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:
Code:
select distinct Country, Product 
from TableA
Query002:
Code:
select distinct Country, Rate 
from TableB 
where Product = '*'
Query003:
Code:
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:
Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,471
Messages
5,414,722
Members
403,544
Latest member
Phx007

This Week's Hot Topics

Top