The first best match is no longer in use

Question

I am not sure how to express this in SQL, if possible, or even call it.

I want for each record in table A the first best matching record in table B that has not yet been selected as the best match. For example, suppose I have a general shopping list and 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 

It’s easy to get Top 1 matching with a query or join:

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

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

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

If it can be done in Access, great. If this cannot be done in Access, but it can be done in another product, this is not ideal, but it works.

Thanks.

Answer 1

This is the 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 

The following are the sides of the table expressions calculated by 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 

Running the LEFT JOIN on FoodType and rn gives you what you want.

Answer 2

Access permits the NOT IN clause. Just write a query to get TOP 1. matching. Then include this query as a subquery inside the NOT IN clause.

 Select * From Table_A A, Table_B B Where A.Food_Type = B.Food_Type And B.Food Not In (Select Top 1 D.Food From Table_A D, Table_B C Where D.Food_Type = D.Food_Type And C.Food_Type = 'give your criterion value here') 

Note that you might want to create a suitable Order By clause to describe how you determine what works best and what doesn't.