How can we find the duplicate values available in a MySQL table by using JOINS?

Suppose we have the following table named ‘stock_item’ in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ as shown in the table.

mysql> Select * from stock_item;
+------------+----------+
| item_name  |quantity  |
+------------+----------+
| Calculator |       89 |
| Notebooks  |       40 |
| Pencil     |       40 |
| Pens       |       32 |
| Shirts     |       29 |
| Shoes      |       29 |
| Trousers   |       29 |
+------------+----------+
7 rows in set (0.00 sec)

Now with the help of the following query using MySQL JOINS we can find the duplicate values in column ‘quantity’ along with the name of items.

mysql> Select distinct g.item_name,g.quantity from stock_item g
   -> INNER JOIN Stock_item b ON g.quantity = b.quantity
   -> WHERE g.item_name<>b.item_name;
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| Pencil    |       40 |
| Notebooks |       40 |
| Shoes     |       29 |
| Trousers  |       29 |
| Shirts    |       29 |
+-----------+----------+
5 rows in set (0.00 sec)

Varma

Published on 20-Feb-2018 12:06:33

• Related Questions & Answers
• How do we find the duplicate values available in a MySQL table?
• How can we update the values in one MySQL table by using the values of another MySQL table?
• In MySQL, how can we maintain data-driven table relationship using joins?
• How can we handle NULL values stored in a MySQL table by using PHP script?
• How can we update values in a MySQL table?
• How can we create a MySQL table by using PHP script?
• How can we create a MySQL temporary table by using PHP script?
• How can we add values into the columns of a MySQL table?
• How to remove duplicate values from a MySQL table using LEFT JOIN?
• How can we delete an existing MySQL table by using PHP script?
• How can we update any value in MySQL view as we can update the values in MySQL table?
• How can we create a MySQL view by selecting some range of values from a base table?
• Can we insert records in a MySQL table without auto_increment values?
• How we can find all the triggers associated with a particular MySQL table?
• How can we check the indexes created by a UNIQUE constraint on a MySQL table?