Select query for partial matches between two tables

[brollogas]

I have 2 tables, Table1 with base codes (ex. MC890) and Table2 with multi level codes (MC890.1.2.3) surrounded by corresponding data. I need a select query to pull all the records from Tabel2 that match MC890 regardless of multiple levels (MC890.*.*.*). I have seen variations of the Like function, but I haven't been able to get anything to work. Please help.

[TjabbeTjibsma]

If you already know what base code you will be searching for in your Table 2, there is no need to join or do a nested select. You would be able to do something like:

SELECT * FROM Table2 WHERE Table2.MLC LIKE 'MC890*'

[brollogas]

Sorry, I should have been more clear. There are many base codes, I just used that one as an example.

[jforbes]

It's a little difficult to communicate this without knowing your exact Table Names and Field Names, but hopefully this gives you an idea:

SELECT Table1.BaseCode, Table2.*
FROM Table1 
LEFT JOIN Table2 
ON Table2.FullCode LIKE Table1.BaseCode & '*'

[brollogas]

@jforbes...this looks like its working. Looks like I might have a little clean up to do, but I think I can move forward. A big thanks to you!

[zmbd]

Have you looked at Database Normalization and Table Structures?

Storing the base code in one table is a start. You then relate the tiers back to this table; however, the exact design is outside of this thread.

[NeoPa]

Indeed. This looks like Table two should have a Compound Index of multiple fields. That way the database system knows that the first part of the index is supposed to match the BaseCode of Table1.

The way you have it now will work, even though it won't show up in an Access QueryDef except in SQL view, but it will not get much benefit from your indexing - which can be extremely important of course.