How would I do a join with "Like" instead of "=" 1

[kellstee]

I have a "lookup" table setup so that inventory coming in will be classed in one of three buckets. The inventory title is the value being referenced and it has lots of crazy values. Samples of titles in the "Data" table are:
"Widget XL"
"Widget Small"
"Widget Medium! Special! Free Shipping!"
"Gizmo Small"
"Gizmo XL"

My lookup table ("Item Mapping") looks like the following:
Inventory Keyword Item
Widget Widget
Gizmo Gizmo

I want to write a SQL statement that updates a field in the Data table ("Item") with the Item value from Item Mapping. How do I do this?? It seems like my join is going to be a "Like" statement. Is that possible?

 

[lynchg]

You would do this by "joining" in your Where clause, with something like this:

Where Left(Inventory.InventoryTitle, Len(Lookup.Keyword) = Lookup.Keyword

 

[PHV]

For me, JetSQL (at least 4.0) supports the Like operator in JOIN:
FROM tblInventory AS A INNER JOIN tblItems AS B ON A.Title Like B.Keyword & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kellstee]

PHV,

I think I just didn't have it written right. Thanks for the post!

Kelly