Big data requires a proper skill set to be able to make meaning out of it. SQL has become a very important tool in a data specialist’s toolbox since it is critical for accessing, updating, inserting, manipulating, and modifying data. While working with a database, you may require to run direct SQL query in Magento 2.
Furthermore, Magento 2 SQL Query for Managing Case-Sensitive Data holds significance in this context. This feature ensures precise handling of data sensitivity to letter casing, facilitating accurate data manipulation and retrieval within the Magento 2 database environment. While working with tables in the database, you may want to compare records between two related tables and find the missing data.
Today, I’ve come up with the SQL query to find missing records between two related tables.
SQL Query to Find Missing Records between Two Related Tables
Let us take a practical example of two database tables.
- eav_attribute_option table
- eav_attribute_option_swatch table
eav_attribute_option table that has a primary key on option_id and reference with the eav_attribute_option swatch table.
| option_id | attribute_id | sort_order |
|---|---|---|
| 1 | 20 | 0 |
| 2 | 128 | 1 |
| 3 | 20 | 2 |
eav_attribute_option_swatch table
| swatch_id | option_id | store_id |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
Here, these two tables have references to each other. Option_id 4 and 5 will not be found in the eav_attribute_option table.
In the above example, we just have a small amount of data, but when it comes to a real-life scenario, databases have a large amount of data and the necessity often arrives to find missing data from related tables.
While migrating the Magento store from Magento 1 to Magento 2, you may observe that some data is missing from the database. Hence, to solve this, the given solution helps you find the missing records between two related tables.
However, the below query is a savior.
SELECT eav_attribute_option_swatch.* FROM eav_attribute_option_swatch LEFT OUTER JOIN eav_attribute_option ON eav_attribute_option_swatch.option_id = eav_attribute_option.option_id WHERE eav_attribute_option.option_id IS NULL
Here, eav_attribute_option_swatch and eav_attribute_option are the default databases of Magento 2.
That’s all!
Please consider sharing this post with Magento Community via social media.
Thank You
