Matching 2 columns from different tables with conditions

Question

Table 1

Order number
11111111
22222222
33333333

Table 2

Order number	Device
111111, 222222	apple
333333	apple

I am trying to use the order numbers in table 1 to match in table 2. If wanted a exact match I use the code below. I have using like but it still doesn't read the order number row in table 2 if there are multiple order numbers.

I need to be able to use table 1 order number to match any of the table 2 order numbers as long as there's a partial match.

SELECT [Table 1].[Order number], [Table 2].[Order number]
FROM [Table 1] 
INNER JOIN [Table 2] ON [Table 1].[Order number] = [Table 2].[Order number]

score 0 · Answer 1 · edited Jan 10 '22 at 20:15

You could probably do something like what is found here: Where value in column containing comma delimited values But, might I suggest that you reconsider your table design? The design you presented is not normalized, which will create a level of difficult you are seeing here (and it will likely get worse). A better design for Table 2 is to make each record its own row. Like this:

ID	OrderNumber	Device
1	111111	Apple
2	222222	Apple
3	333333	Apple

Each record has it's own row, with a primary key that uniquely identifies the record. This will make querying a lot simpler. This is considered normalized: https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description

I would love to be able to redesign the table but I am working in existing database 100k plus rows. — Wesley Green, Jan 10 '22 at 19:18

score 0 · Answer 2 · answered Jan 10 '22 at 20:45

In your state there are four possible variations on matching:

The number is at the begining,
The number is in the middle,
The number is at the end.
Exactly same

To cover all of these situations you can simply create conditions and append them with "OR" statement.

In the final form your query will be look like this:

SELECT
    [Table 1].[Order number], 
    [Table 2].[Order number]
FROM 
    [Table 1]
    INNER JOIN [Table 2]
    ON  (
            [Table 1].[Order number]=[Table 2].[Order number] 
            OR [Table 2].[Order number] LIKE [Table 1].[Order number] & ",%"
            OR [Table 2].[Order number] LIKE "%," & [Table 1].[Order number] & ",%"
            OR [Table 2].[Order number] LIKE "%," & [Table 1].[Order number]
        )

Matching 2 columns from different tables with conditions

2 Answers2