First Best Match Not Already Used

Question

I'm not sure how to express this in SQL, if it's even possible, or what to even call it.

I want, for each record in Table A, the first best matching record in Table B that wasn't already picked as a best match. For example, suppose I have a Generic Shopping List and a Food Menu:

Table A                      Table B
Generic Shopping List        Food Menu
---------------------        ----------------------
Food Type                    Food        Food Type
---------------------        ----------------------
Meat                         Tomatoes    Vegetable
Meat                         Lettuce     Vegetable
Vegetable                    Bacon       Vegetable
Vegetable                    Bacon       Meat
Vegetable                    Beef        Meat
Vegetable                    Apple       Fruit
Fruit                        Orange      Fruit
Fruit                        Bacon       Fruit
Dairy                        Milk        Dairy
                             Cheese      Dairy
                             Yogurt      Dairy

With a query or join, it's easy to get the Top 1 match:

Table/Query C
Automagic Shopping
------------------
Food
------------------
Bacon
Bacon
Tomatoes
Tomatoes
Tomatoes
Tomatoes
Apple
Apple
Milk

I know how to do that, and because I like bacon, I could live with this. Unfortunately, I really need the full breadth of the available food options, such that I have slots available for it.

Table/Query C
Better Magic Shopping
---------------------
Food
---------------------
Bacon
Beef
Tomatoes
Lettuce
Bacon
<NULL - No More Available Matches - Don't Care>
Apple
Orange
Milk

If this can be done in Access, great. If it can't be done in Access, but it can be done in another product, it isn't ideal, but it's workable.

Thanks.

Answer 1

This is a way to do it in SQL Server:

SELECT t1.FoodType, t2.Food
FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY FoodType ORDER BY FoodType) AS rn
   FROM #tableA ) AS t1
LEFT  JOIN (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY FoodType ORDER BY FoodType) AS rn
   FROM #tableB) AS t2 ON t1.FoodType = t2.FoodType AND t1.rn = t2.rn

Below are, side by side, the table expressions computed by the two subqueries, t1, t2:

Results for t1:       Results for t2:
FoodType    rn        Food      FoodType    rn
---------------      --------------------------
Dairy       1         Milk      Dairy       1
Fruit       1         Cheese    Dairy       2
Fruit       2         Yogurt    Dairy       3
Meat        1         Apple     Fruit       1
Meat        2         Orange    Fruit       2
Vegetable   1         Bacon     Fruit       3
Vegetable   2         Bacon     Meat        1
Vegetable   3         Beef      Meat        2
Vegetable   4         Tomatoes  Vegetable   1
                      Lettuce   Vegetable   2
                      Bacon     Vegetable   3

Doing a LEFT JOIN on FoodType and rn gets you what you want.