How to Match Multiple Fields Between Product and Fitment Tables
Question details
The user needs to match records between a product table and a fitment table using multiple dimensions (length, width, and depth) as criteria without storing redundant data like SKUs.
- Product
- Database and Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing product databases where a single dimension is insufficient for a match, requiring a composite key approach across multiple fields.
- Observed behavior
- The goal is to successfully join two tables using length, width, and depth to retrieve the corresponding SKU dynamically and accurately.
Ensure that the length, width, and depth fields in both the product and fitment tables have consistent data types, standardized units, and no trailing spaces to prevent matching errors.
Use a SQL Join with a Composite Key
Join the product and fitment tables on all three fields to retrieve data without creating redundancy.
When a single identifier isn't enough, multiple fields can act as a composite foreign key. This allows you to retrieve the corresponding SKU by joining the tables, keeping your database normalized and avoiding duplicate SKU entries in the fitment table.
Launch your database management tool (such as MySQL, MS Access, or SQL Server) and navigate to the SQL query editor.
Create a new SQL query to select the desired fields, including the SKU from your product table.
Use an INNER JOIN specifying the three matching conditions: ON Product.Length = Fitment.Length AND Product.Width = Fitment.Width AND Product.Depth = Fitment.Depth.
Run the query to instantly retrieve the associated SKU dynamically for each matching fitment.
Filter Fitments using Form Inputs (MS Access)
Create a query that dynamically filters fitment records based on user input from a form containing length, width, and depth.
Match Multiple Fields Easily in WPS Spreadsheet
If you are managing your product and fitment tables in a spreadsheet instead of a relational database, you can use WPS Spreadsheet to match multiple conditions seamlessly using a helper column and lookup functions.
- 1. Open your tables in WPS Spreadsheet: Launch WPS Office and open the workbook containing your product and fitment tables.
- 2. Create a Helper column: Insert a new column in both tables. Concatenate the Length, Width, and Depth fields using a formula like =A2&"-"&B2&"-"&C2.
- 3. Use VLOOKUP or INDEX/MATCH: In your target table, use the VLOOKUP function or INDEX and MATCH to search for the combined string in the newly created Helper column of the source table.
- 4. Retrieve the SKU: Set the formula to return the SKU column. Drag the fill handle down to apply the matching logic to all rows in your dataset instantly.
Frequently Asked Questions
What is a composite key in data matching?
A composite key consists of two or more attributes (such as length, width, and depth) that together uniquely identify a record in a table. It ensures accurate data joins when a single field is not unique enough to identify a specific item.
Why shouldn't I just copy the SKU directly into the fitment table?
Storing the SKU in the fitment table when it can be derived from the dimensional fields causes redundant data. Following database normalization principles, it's better to retrieve the SKU dynamically using a table join to prevent update anomalies and keep the database clean.
Can I match multiple fields in a spreadsheet without using SQL?
Yes. In spreadsheet applications like WPS Spreadsheet or Microsoft Excel, you can match multiple fields by concatenating the fields into a single 'helper' column, or by utilizing array formulas with the INDEX and MATCH functions to check multiple criteria simultaneously.
