Businesses are collecting and churning out billion gigabytes of data every day. 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 database, you may require to run direct SQL query in Magento 2.
While working with tables in 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 reference with each other. The 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 necessity often arrives to find missing data from related tables.
While migrating Magento store from Magento 1 to Magento 2, you may observe that some data is missing from the database. Hence, to solve this, given solution helps you find the missing records between two related tables.
However, the below query is a savior.
1 2 3 4 5 | 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 default databases of Magento 2.
That’s all!
If you have any question regarding this, do mention in below comment section.
I would be happy to answer.
Please do consider sharing this post with Magento Community via social media.
Thank You
Get Weekly Updates
Never miss Magento tips, tricks, tutorials, and news.
Thank you for subscribing.
Something went wrong.
