CODEWITHSUNDEEP
mysqlduplicates
Roger
Roger

Reputation: 13

MySQL: Finding duplicates across multiple fields

Background: my employer has a database powered by a really old version of MySQL (3.23). I have been asked to find duplicate serial numbers and MAC addresses in the database.

I was able to find the duplicate serial numbers, but since this version of MySQL doesn't support subqueries, I had to resort to using a temporary table. These are the two SQL statements I ended up using:

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_Duplicate_Serials
SELECT Serial
FROM Inventory
WHERE Serial IS NOT NULL
GROUP BY Serial
HAVING COUNT(Serial) > 1

SELECT DeviceName, Model, Inventory.Serial
FROM Inventory
INNER JOIN Inventory_Duplicate_Serials
ON Inventory.Serial = Inventory_Duplicate_Serials.Serial
ORDER BY Serial

Now I need to find the duplicate MAC addresses. The problem is the "Inventory" table has three MAC address fields (MAC, MAC2, and MAC3). So, for example, if the value of an item's "MAC" field is the same as the value of another item's "MAC2" field, I need to know about it. How do I go about doing this? Thank you for your time.

UPDATE: Solved. I ended up creating two temporary tables (Inventory_All_MACs and Inventory_Duplicate_MACs). These are the five queries:

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_All_MACs
SELECT MAC
FROM Inventory
WHERE MAC != ''

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_All_MACs
SELECT MAC2 AS MAC
FROM Inventory
WHERE MAC2 != ''

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_All_MACs
SELECT MAC3 AS MAC
FROM Inventory
WHERE MAC3 != ''

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_Duplicate_MACs
SELECT MAC
FROM Inventory_All_MACs
GROUP BY MAC
HAVING COUNT(MAC) > 1

SELECT DeviceName, Model, Inventory_Duplicate_MACs.MAC AS DuplicateMAC, Inventory.MAC, MAC2, MAC3
FROM Inventory_Duplicate_MACs
INNER JOIN Inventory
ON Inventory.MAC  = Inventory_Duplicate_MACs.MAC
OR Inventory.MAC2 = Inventory_Duplicate_MACs.MAC
OR Inventory.MAC3 = Inventory_Duplicate_MACs.MAC
ORDER BY Inventory_Duplicate_MACs.MAC, DeviceName, Model

Thanks everybody!

Upvotes: 1

Views: 587

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_Mac
  SELECT Mac
  FROM Inventory

INSERT INTO Inventory_Mac
  SELECT Mac2
  FROM Inventory

INSERT INTO Inventory_Mac
  SELECT Mac3
  FROM Inventory

CREATE TEMPORARY TABLE IF NOT EXISTS Inventory_Duplicate_Mac
  SELECT Mac, COUNT(*) AS cnt
  FROM Inventory_Mac
  GROUP BY Mac
  HAVING COUNT(*) > 1


SELECT DeviceName, Model, im.Mac, i.Mac, i.Mac2, i.Mac3
FROM Inventory_Duplicate_Mac AS im
  JOIN Inventory AS i
    ON    i.Mac  = im.Mac
       OR i.Mac2 = im.Mac
       OR i.Mac3 = im.Mac
ORDER BY im.Mac

Upvotes: 0

Leopold Stotch
Leopold Stotch

Reputation: 1522

Not 100% sure on this answer but it could be worth a try using LEFT JOINS e.g:

SELECT address1
FROM addresses
LEFT JOIN Inventory_Duplicate_Addresses ad1
ON Addresses.MAC = ad1.mac
LEFT JOIN Inventory_Duplicate_Addresses ad2
ON Addresses.MAC = ad2.mac2
LEFT JOIN Inventory_Duplicate_Addresses ad3
ON Addresses.MAC = ad3.mac3

Upvotes: 0

Serj Sagan
Serj Sagan

Reputation: 30208

A 'simple' solution that comes to mind is to create a second temporary table that lists all MAC addresses in one column, so you would need to create three entries for one entry from the first temporary table.

Upvotes: 1

Related Questions