SQL to pull duplicated data from one column or another

Slinkilicious 0
Viewed 44

I've been struggling with creating an SQL script in Access that will pull some columns from a table if the data is duplicated in one column or another. My example table looks like this:

title MAC Online time
PC1 ab-bc-cd-ef-12 Online Today
PC2 ab-bc-cd-ef-12 Offline Yesterday
PC1 ab-bc-89-c5-78 Offline 1 year
PC4 a6-65-bf-33-01 Online Today

and I'm trying to get an output similar to:

title MAC Online time
PC1 ab-bc-cd-ef-12 Online Today
PC2 ab-bc-cd-ef-12 Offline Yesterday
PC1 ab-bc-89-c5-78 Offline 1 year

I have put together this SQL so far:

select
    s.title,
    MAC,
    time,
    online
FROM
    Table1 AS s
    INNER JOIN ( SELECT title FROM Table1 GROUP BY title HAVING count(*) > 1) AS t
    INNER JOIN ( SELECT MAC FROM Table1 GROUP BY MAC HAVING count(*) > 1) AS u ON
        s.title = t.title AND s.MAC = u.MAC

Running it however bring up there is a Syntax error in the FROM clause. I have tried adding the parenthesis into the inital FROM statement like:

select
    s.title,
    MAC,
    time,
    online
FROM
    (
        (
            Table1 AS s
            INNER JOIN ( SELECT title FROM Table1 GROUP BY title HAVING count(*) > 1 ) AS t
        )
        INNER JOIN
        (
            SELECT MAC FROM Table1 GROUP BY MAC HAVING count(*) > 1
        ) AS u 
            ON s.title = t.title AND s.MAC = u.MAC

Anywhere I add further parenthesis will highlight it as the error when I try to run it, but removing them highlights the second JOIN statement as the error.

I'm sure there are probably other errors in the code or even a better way to write this. Any help is greatly appreciated.

ms-access sql
2 Answers

Try this union query.

SELECT tblDuplicate.Title, tblDuplicate.MAC, tblDuplicate.Online, tblDuplicate.OnlineTime FROM tblDuplicate INNER JOIN (SELECT tblDuplicate.Title, Count(tblDuplicate.Title) AS CountOfTitle FROM tblDuplicate GROUP BY tblDuplicate.Title HAVING (((Count(tblDuplicate.Title))>1))) as t1 ON tblDuplicate.Title = t1.Title;
    union
SELECT tblDuplicate.Title, tblDuplicate.MAC, tblDuplicate.Online, tblDuplicate.OnlineTime FROM tblDuplicate INNER JOIN (SELECT tblDuplicate.MAC, Count(tblDuplicate.MAC) AS CountOfMAC FROM tblDuplicate GROUP BY tblDuplicate.MAC HAVING (((Count(tblDuplicate.MAC))>1))) as t2 ON tblDuplicate.MAC = t2.MAC;
Harun24hr0

So this is kinda working:

SELECT s.Name, s.[MAC Addresses], s.[Offline Date], s.state
  FROM Table1 AS s 
  INNER JOIN (SELECT name FROM Table1 GROUP BY name HAVING count(*) > 1)  AS t ON s.name = t.name 
UNION ALL 
  SELECT s.Name, s.[MAC Addresses], s.[Offline Date], s.state
  FROM Table1 AS s 
  INNER JOIN (SELECT [MAC Addresses] FROM Table1 GROUP BY [MAC Addresses] HAVING count(*) > 1) AS u ON s.[MAC Addresses] = u.[MAC Addresses];

Apologies for the change in record names. I was running these tests on a separate test table than before with my actual imported data.

The output of this table is a little messy as it puts in both tables mashed together and if 2 records are almost exact duplicates, then it might be a bit hard to read. I believe it is robust enough to handle if only one column is duplicated at all. I'll try to run this on a larger set of tables with production data to check.

Slinkilicious0
Related