Access SQL: left join with wildcard

Question

This seems like it should be easier than it is.

I'm trying to join two tables together in Access. They join on the field "product". One is a reference table containing fee rates for a subset of the products, and the other is sales data of all products.

The issue is that the sales products can have a leading designation of two characters added to the product. For example, a product could be sold as T2202, or U-T2202. I need to pick these variants of the products from the reference table, but I need the non-relevant products filtered out still.

My current attempts haven't worked at all; they either leave in too much or take out too little. Attempt 1 (note these are pared-down examples as there are a bunch of other non-relevant conditions)

SELECT *
FROM table1 LEFT JOIN table2 on table1.product LIKE "*" & table2.product

Attempt 2

SELECT *
FROM table1 LEFT JOIN table2 ON table1.product = table2.product
WHERE table1.product LIKE "*" & table2.product

Alternatively, I could add copies of the data to the reference table with the leading characters added, but this isn't really ideal.

Thank you in advance.

Same examples of it taking out too much/too little would help. Can you show some results from the query using data to help illustrate the problem? — xQbert, Aug 14 '17 at 14:35
Please read & act on [mcve]. Give full DDL & DML that runs. Do you have PKs, FKs & indexes? What do "previous attempts" & "haven't worked at all" mean? Why are you using LEFT JOIN ON?--It is INNER JOIN ON rows plus unmatched left table row extended by NULLs. If you are nulling unmatched reference table rows then you need to have all your matching done in the ON. WHERE is applied after all JOINs including all NULL extensions. — philipxy, Aug 14 '17 at 20:35
[Smart id/keys are an antipattern.](https://stackoverflow.com/a/34082143/3404097) Although these *aren't even* ids/keys, because they don't uniquely identify. — philipxy, Aug 14 '17 at 20:49

score 0 · Answer 1 · answered Aug 14 '17 at 14:13

0

SELECT *
FROM table1 LEFT JOIN table2 ON table1.product = table2.product 
                             OR table1.product = CONCAT('U-', table2.product)

If you have the list of prefixes you may add more OR statements

answered Aug 14 '17 at 14:13

StanislavL

56,971
9
68
98

I tried this approach and the query never completed. Perhaps I need to leave it longer (at around 10 mins now), but the amount of data isn't that big (<5000 rows). – Aidan Hoffman Aug 14 '17 at 14:51
StanislavL: You mean `table2.product = CONCAT('U-', table1.product)`. You could also explain how WHERE is applied after joins & null extensions, which @AidanHoffman clearly doesn't understand. – philipxy Aug 14 '17 at 20:43

score 0 · Answer 2 · answered Aug 14 '17 at 14:31

I would have expected your solution to work, but I'm not familiar with Access, maybe there are some extra prerequisites like quotes or something like that.

You could try this instead:

SELECT    *
FROM      table1 
LEFT JOIN table2 
       on table1.product = right(table2.product, len(table1.product))

This should match table1.product with a substring of table2.product where all right-handed characters are taken up to a length of table1.product.

Of course an even better solution (though probably not helpful for you at this time) is to sanitize input and have referential integrity forced by your database. That way you'll never have this issue in the first place.

Access SQL: left join with wildcard

2 Answers2