SQL Query to Find Missing Records between Two Related Tables

SQL Query to Find Missing Records between Two Related Tables

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.

  1. eav_attribute_option table
  2. 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_idattribute_idsort_order
1200
21281
3202

eav_attribute_option_swatch table

swatch_idoption_idstore_id
110
220
330
440
550

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.

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.

5
(based on 10 Reviews)

An expert in his field, Jignesh is the team leader at Meetanshi. His passion for Magento has inspired others in the team too. Apart from work, he is a cricket lover.

Leave a reply:

Your email address will not be published.