MySQL Forums

I'm trying to match 2 tables based on a common order #: Table$User with field OrderNumber and Table_Items with field ItemNumbers

OrderNumber must be found within ItemNumbers - but all search fields are written as variables to produce a list based on the items a user has chosen (Table$User - where $User = their account #)

I have:

my $dbh = $db->prepare("SELECT Table$User.baseNum,Table$User.Specs,Table$User.PricePaid,Table_Items.Title,Table_Items.Image FROM Table$User, Table_Items WHERE Table_Items.ItemNumbers LIKE %Table$User.OrderNumber%");

When I make it just = instead of LIKE with wildcards, then it works as long as Table_Items.ItemNumbers is only ONE number that matches the Order Number. The problem is that most of the ItemNumbers fields include multiple item numbers (it's just how the product is... several item #'s over the years for the same Title - each item # belonging to a different color, but we do not want to list out each color separately)... so the ItemNumbers field might look like: 1000.[]1001.[]100010. (The period is part of each number)

If an item number is only 1000 and the customer's choice was 1000, then of course it works. But, when trying to find 1000. in the above example, it brings back an empty result.

Can you help??

MySQL - 5.0.45 is my version